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