DATA Step, Macro, Functions and more

Proc transpose

Reply
Occasional Contributor
Posts: 11

Proc transpose

I have data in mentioned below format

 

Col-1Col-2Col-3Col-4
product-1LAI125.00
product-1LEX217.50
product-2LAI19.00
product-2LEX125.00
product-3LAI116.20
product-3LEX1199.00
product-4LAI130.00
product-4LEX488.00

 

my requirement is to see in the below-transposed format

 

Col-1LAI_Col-3LEX_Col-3LAI_Col-4LEX_Col-4
product-1122517.5
product-211925
product-31116.2199
product-4143088

 

 

Super User
Posts: 23,267

Re: Proc transpose

PROC Star
Posts: 1,571

Re: Proc transpose

demo:

you need a double transpose with careful grouping variables i.e you should know how to group 

 

data have;
infile cards truncover;
input Col1 :$20.	Col2 $	Col3	Col4 ;
cards;
product-1	LAI	1	25.00
product-1	LEX	2	17.50
product-2	LAI	1	9.00
product-2	LEX	1	25.00
product-3	LAI	1	16.20
product-3	LEX	1	199.00
product-4	LAI	1	30.00
product-4	LEX	4	88.00
;
proc transpose data=have out=w prefix=l;
by col1 col2;
var  col3 col4;
run;
proc transpose data=w out=final_want(drop=_:)  prefix=l ;
by col1 ;
var l1;
run;
Occasional Contributor
Posts: 11

Re: Proc transpose

Posted in reply to novinosrin

It was a sample example and there are only two value columns (column3 and column4), my problem is I have multiple value column, by this example I have to transpose it so many times like that. Can we run any loop or something which can take all value column and run in the smaller piece of code?

PROC Star
Posts: 1,571

Re: Proc transpose

Just getting the order of vars right with an added sort:

 


data have;
infile cards truncover;
input Col1 :$20.	Col2 $	Col3	Col4 ;
cards;
product-1	LAI	1	25.00
product-1	LEX	2	17.50
product-2	LAI	1	9.00
product-2	LEX	1	25.00
product-3	LAI	1	16.20
product-3	LEX	1	199.00
product-4	LAI	1	30.00
product-4	LEX	4	88.00
;
proc transpose data=have out=w prefix=l;
by col1 col2;
var  col3 col4;
run;
proc sort data=w;
by col1 _name_;
run;
proc transpose data=w out=final_want(drop=_:)  prefix=l ;
by col1 ;
var l1;
run;
Occasional Contributor
Posts: 11

Re: Proc transpose

Posted in reply to novinosrin

It was a sample example and there are only two value columns (column3 and column4), my problem is I have multiple value column, by this example I have to transpose it so many times like that. Can we run any loop or something which can take all value column and run in the smaller piece of code?

PROC Star
Posts: 1,571

Re: Proc transpose

I'd recommend trying @art297's transpose macro if that can help. The details are available in the library. If he has the time, I'm sure Art will point you in the right direction

PROC Star
Posts: 8,146

Re: Proc transpose

Posted in reply to novinosrin

@Srigyan: Unless I'm missing something, I think that a slight variant of @novinosrin's code would meet your need, regardless of the number of values you have for Col2 and Col3:

data have;
  infile cards truncover;
  input Col1 :$20.    Col2 $  Col3    Col4 ;
  cards;
product-1   LAI 1   25.00
product-1   LEX 2   17.50
product-2   LAI 1   9.00
product-2   LEX 1   25.00
product-3   LAI 1   16.20
product-3   LEX 1   199.00
product-4   LAI 1   30.00
product-4   LEX 4   88.00
;

proc sort data=have;
  by col1 col2;
run;

proc transpose data=have out=w prefix=l;
  by col1 col2;
  var  col3 col4;
run;

data w;
  set w;
  _name_=catx('_',col2,_name_);
run;

proc transpose data=w out=final_want(drop=_:);
  by col1 ;
  var l1;
  id _name_;
run;

Art, CEO, AnalystFinder.com

 

Super User
Super User
Posts: 7,932

Re: Proc transpose

Art - How is building your own variable name 

data w;
  set w;
  _name_=catx('_',col2,_name_);
run;

proc transpose data=w out=final_want(drop=_:);
  by col1 ;
  var l1;
  id _name_;
run;

Different than letting SAS build the variable name?

proc transpose data=w out=final_want(drop=_:);
  by col1 ;
  var l1;
  id col2 _name_;
run;
PROC Star
Posts: 8,146

Re: Proc transpose

@Tom: It added in the _ delimiter that @Srigyan indicated was wanted between the col2 and col3 values.

 

Otherwise, I agree, they both do the same thing.

 

Art, CEO, AnalystFinder.com

Super User
Super User
Posts: 7,932

Re: Proc transpose

That is what the DELIMITER= option on the PROC TRANSPOSE statement is for.

DELIMITER= delimiter

specifies a delimiter to use in constructing names for transposed variables in the output data set. If specified, the delimiter will be inserted between variable values if more than one variable has been specified on the ID statement.

Alias: DELIM=

 

PROC Star
Posts: 8,146

Re: Proc transpose

@Tom: Agreed! The extra datastep can be avoided:

data have;
  infile cards truncover;
  input Col1 :$20.    Col2 $  Col3    Col4 ;
  cards;
product-1   LAI 1   25.00
product-1   LEX 2   17.50
product-2   LAI 1   9.00
product-2   LEX 1   25.00
product-3   LAI 1   16.20
product-3   LEX 1   199.00
product-4   LAI 1   30.00
product-4   LEX 4   88.00
;

proc sort data=have;
  by col1 col2;
run;

proc transpose data=have out=w prefix=l;
  by col1 col2;
  var  col3 col4;
run;

proc transpose data=w out=want (drop=_:) delimiter=_;
  by col1 ;
  var l1;
  id col2 _name_;
run;

Art, CEO, AnalystFinder.com

 

Super User
Posts: 10,689

Re: Proc transpose

OR Merge Skill proposed by me,Arthur.T,Matt .

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

 

 

data have;
infile cards truncover;
input Col1 :$20.	Col2 $	Col3	Col4 ;
cards;
product-1	LAI	1	25.00
product-1	LEX	2	17.50
product-2	LAI	1	9.00
product-2	LEX	1	25.00
product-3	LAI	1	16.20
product-3	LEX	1	199.00
product-4	LAI	1	30.00
product-4	LEX	4	88.00
;
proc sql noprint;
select distinct catt('have(where=(col2="',col2,'")
rename=(col3=',col2,'_col3 col4=',col2,'_col4))')
into : list separated by ' '
 from have;
quit;
data want;
 merge &list;
 by col1 ;
 drop col2;
run;
Ask a Question
Discussion stats
  • 12 replies
  • 143 views
  • 3 likes
  • 6 in conversation