Base SAS - Proc SQL


Need BASE SAS - PROC SQL Code . I have a table with following data

ID Contact          Preference_ID
1  1111111111       Preference1
1  [email protected]   Preference2
1  3333333333       Preference4
2  4444444444       Preference1
2  [email protected]   Preference5

Here I have ID, Mobile, Email and Preference_ID columns. Preference_ID column can take values from Preference1 to Preference5

I want to write a Base SAS - Proc SQL Query which can give me data in the following format

ID Preference1 Preference2    Preference3 Preference4 Preference5
1  1111111111  [email protected]              3333333333
2  4444444444                                        [email protected]

Means Preference_ID row should become column of the output data and contact details should be filled row wise according to the ID.

- - Source

Answers

answered 1 week ago Kiran #1

use proc tranpose as @Tom Suggests it is easy. Proc SQL will involve lot of hard coding.

data have;
 input ID Contact:$20.       Preference_ID:$11.;
datalines;
1  1111111111       Preference1
1  [email protected]   Preference2
1  3333333333       Preference4
2  4444444444       Preference1
2  [email protected]   Preference5
;



proc transpose data = have out=want(drop=_name_);
by id; id preference_id;
var contact;
run;



 proc sql;
 create table want as 
 select id,
   max(case when Preference_ID="Preference1" then contact end) as Preference1,
   max(case when Preference_ID="Preference2" then contact end) as Preference2,
   max(case when Preference_ID="Preference3" then contact end) as Preference3,
   max(case when Preference_ID="Preference4" then contact end) as Preference4,
   max(case when Preference_ID="Preference5" then contact end) as Preference5
from have
group by id
 ; 

comments powered by Disqus