Proc transpose

[Srigyan]

I have data in mentioned below format

Col-1	Col-2	Col-3	Col-4
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

my requirement is to see in the below-transposed format

Col-1	LAI_Col-3	LEX_Col-3	LAI_Col-4	LEX_Col-4
product-1	1	2	25	17.5
product-2	1	1	9	25
product-3	1	1	16.2	199
product-4	1	4	30	88

[Reeza]

See Example 3 and beyond here: https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

[novinosrin]

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;

[Srigyan]

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?

[novinosrin]

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;

[Srigyan]

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?

[novinosrin]

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

[art297]

@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

[Tom]

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;

[art297]

@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

[Tom]

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=

[art297]

@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

[Ksharp]

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;