Help using Base SAS procedures

Proc Transpose

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Proc Transpose

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


Accepted Solutions
Solution
‎08-23-2013 09:43 AM
Regular Contributor
Posts: 151

Re: Proc Transpose

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;

View solution in original post


All Replies
Super User
Super User
Posts: 6,502

Re: Proc Transpose

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;

Super Contributor
Posts: 1,040

Re: Proc Transpose

Hi Tom,

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

Thanks

Solution
‎08-23-2013 09:43 AM
Regular Contributor
Posts: 151

Re: Proc Transpose

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;

Occasional Contributor
Posts: 12

Re: Proc Transpose

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

Smiley Happy

Occasional Contributor
Posts: 14

Re: Proc Transpose

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 225 views
  • 3 likes
  • 5 in conversation