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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.