topic Re: Proc transpose in SAS Programming

Proc transpose

Srigyan — Thu, 31 May 2018 15:52:44 GMT

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

Re: Proc transpose

Reeza — Thu, 31 May 2018 15:58:52 GMT

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

Re: Proc transpose

novinosrin — Thu, 31 May 2018 16:27:16 GMT

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;

Re: Proc transpose

novinosrin — Thu, 31 May 2018 16:31:09 GMT

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;

Re: Proc transpose

Srigyan — Fri, 01 Jun 2018 10:30:21 GMT

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?

Re: Proc transpose

Srigyan — Fri, 01 Jun 2018 10:31:13 GMT

Re: Proc transpose

novinosrin — Fri, 01 Jun 2018 11:57:15 GMT

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

Re: Proc transpose

art297 — Fri, 01 Jun 2018 12:43:37 GMT

@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

Re: Proc transpose

Ksharp — Fri, 01 Jun 2018 14:01:35 GMT

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;

Re: Proc transpose

Tom — Fri, 01 Jun 2018 14:35:11 GMT

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;

Re: Proc transpose

art297 — Fri, 01 Jun 2018 14:43:45 GMT

@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

Re: Proc transpose

Tom — Fri, 01 Jun 2018 14:52:01 GMT

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=

Re: Proc transpose

art297 — Fri, 01 Jun 2018 15:14:47 GMT

@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