Hi All,
I want to transpose the following dataset
id | d | amt |
x001 | 12 | 1000 |
x002 | 10 | 1000 |
x002 | 11 | 2000 |
x002 | 12 | 3000 |
x003 | 13 | 1000 |
x003 | 15 | 3000 |
x004 | 23 | 1000 |
x005 | 13 | 4000 |
x005 | 14 | 2000 |
x005 | 16 | 3000 |
x005 | 17 | 4000 |
x005 | 18 | 1000 |
and I am trying to get following output
d1 | d2 | d3 | d4 | d5 | a1 | a2 | a3 | a4 | a5 | |
x001 | 12 | 1000 | ||||||||
x002 | 10 | 11 | 12 | 1000 | 2000 | 3000 | ||||
x003 | 13 | 15 | 1000 | 3000 | ||||||
x004 | 23 | 1000 | ||||||||
x005 | 13 | 14 | 16 | 17 | 18 | 4000 | 2000 | 3000 | 4000 | 1000 |
However, if I write the following program I get values of different columns in different rows.
proc transpose data=Y out=check ;
id ID;
var d amt;
run;
I tried the same. Transposed and then merged. I was wondering if there is any other option.
I think you need to do transposes and then do a merge of those two datasets.
Or you can use a really handy macro written by some users on here called 'A better way to flip'
http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset
I think you need to do transposes and then do a merge of those two datasets.
Or you can use a really handy macro written by some users on here called 'A better way to flip'
http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset
I tried the same. Transposed and then merged. I was wondering if there is any other option.
1) Yes. there are another option. MERGE skill proposed by Me,Arthur,Matt. http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf data have; infile cards expandtabs truncover; input id $ d amt; cards; x001 12 1000 x002 10 1000 x002 11 2000 x002 12 3000 x003 13 1000 x003 15 3000 x004 23 1000 x005 13 4000 x005 14 2000 x005 16 3000 x005 17 4000 x005 18 1000 ; run; data have; set have; by id; if first.id then n=0; n+1; run; proc sql; select distinct catt('have(where=(n=',n,') rename=(d=d',n,' amt=amt',n,'))') into : merge separated by ' ' from have; quit; data want; merge &merge; by id; drop n; run; proc print;run; 2) Or another option is IDGROUPS data have; infile cards expandtabs truncover; input id $ d amt; cards; x001 12 1000 x002 10 1000 x002 11 2000 x002 12 3000 x003 13 1000 x003 15 3000 x004 23 1000 x005 13 4000 x005 14 2000 x005 16 3000 x005 17 4000 x005 18 1000 ; run; proc sql; select max(n) into :n from (select count(*) as n from have group by id); quit; proc summary data=have; by id; output out=want idgroups(out[&n] (d amt)=); run; proc print;run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.