I want to transpose a dataset like this
Month Customer
1 AAAAA
2 AAAAA
3 AAAAA
4 AAAAA
1 BBBBB
2 BBBBB
to
1 2 3 4 5
AAAAA AAAAA AAAAA AAAAA AAAAA
BBBBB BBBBB
data c;
infile cards;
input month customer : $5.;
cards;
1 AAAAA
2 AAAAA
3 AAAAA
4 AAAAA
1 BBBBB
2 BBBBB
;
proc sort data=c; by month;run;
proc transpose data=c out=c1; by month; var customer; run;
proc transpose data=c1 (drop=_name_) out=c2; var month col:; run;
You need a third variable that indicates when to start a new row in the output dataset.
The code below creates one by assuming when month number is less than previous month value then you have started a new group.
data have ;
input month customer $;
if month < lag(month) then group+1;
cards;
1 AAAAA
2 AAAAA
3 AAAAA
4 AAAAA
1 BBBBB
2 BBBBB
;;;;
proc transpose data=have out=want ;
by group ;
id month;
var customer;
run;
proc print width=min;
run;
Hi Tom,
Before we transpose, the dataset was sorted by CUSTOMER and BY MONTH???
Thanks
data c;
infile cards;
input month customer : $5.;
cards;
1 AAAAA
2 AAAAA
3 AAAAA
4 AAAAA
1 BBBBB
2 BBBBB
;
proc sort data=c; by month;run;
proc transpose data=c out=c1; by month; var customer; run;
proc transpose data=c1 (drop=_name_) out=c2; var month col:; run;
Thanks Oleg_L, I got exactly what I want without any intermediate blanks.
data have ;
input month customer $;
cards;
1 AAAAA
2 AAAAA
3 AAAAA
4 AAAAA
1 BBBBB
2 BBBBB
;
Run;
option validvarname = any;
proc transpose data = have out = tran(drop = _name_ customer);
id month;
by customer;
var customer;
Run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: