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;
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 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.