BookmarkSubscribeRSS Feed
Srigyan
Quartz | Level 8

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

 

 

12 REPLIES 12
novinosrin
Tourmaline | Level 20

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
Quartz | Level 8

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
Tourmaline | Level 20

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
Quartz | Level 8

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
Tourmaline | Level 20

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
Opal | Level 21

@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
Super User Tom
Super User

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
Opal | Level 21

@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
Super User Tom
Super User

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
Opal | Level 21

@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
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1575 views
  • 3 likes
  • 6 in conversation