topic Re: Proc Transpose in SAS Procedures

Proc Transpose

SOORISAS — Fri, 23 Aug 2013 13:11:46 GMT

I want to transpose a dataset like this

Month Customer

1 AAAAA

2 AAAAA

3 AAAAA

4 AAAAA

1 BBBBB

2 BBBBB

1 2 3 4 5

AAAAA AAAAA AAAAA AAAAA AAAAA

BBBBB BBBBB

Re: Proc Transpose

Tom — Fri, 23 Aug 2013 13:31:44 GMT

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;

Re: Proc Transpose

Oleg_L — Fri, 23 Aug 2013 13:43:45 GMT

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;

Re: Proc Transpose

robertrao — Fri, 23 Aug 2013 15:13:45 GMT

Hi Tom,

Before we transpose, the dataset was sorted by CUSTOMER and BY MONTH???

Thanks

Re: Proc Transpose

Pallav — Sat, 24 Aug 2013 07:47:17 GMT

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;

Re: Proc Transpose

SOORISAS — Tue, 27 Aug 2013 11:09:59 GMT

Thanks Oleg_L, I got exactly what I want without any intermediate blanks.