BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
deega
Quartz | Level 8

Hi All,

I want to transpose the following dataset 

iddamt
x001121000
x002101000
x002112000
x002123000
x003131000
x003153000
x004231000
x005134000
x005142000
x005163000
x005174000
x005181000

 

and I am trying to get following output

 d1d2d3d4d5a1a2a3a4a5
x00112    1000    
x002101112  100020003000  
x0031315   10003000   
x00423    1000    
x00513141617184000200030004000

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;

1 ACCEPTED SOLUTION

Accepted Solutions
deega
Quartz | Level 8

I tried the same. Transposed and then merged. I was wondering if there is any other option.

View solution in original post

4 REPLIES 4
Reeza
Super User

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

Reeza
Super User

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

deega
Quartz | Level 8

I tried the same. Transposed and then merged. I was wondering if there is any other option.

Ksharp
Super User
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;


sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4082 views
  • 1 like
  • 3 in conversation