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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.