BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sivastat08
Pyrite | Level 9

Input Data:

AccountParameterProdCom
Act1Par102
Act1Par201
Act1Par323
Act2Par112
Act2Par223
Act3   

 

 

Output Format:

AccountParameter_1Prod_1Com_1Parameter_2Prod_2Com_2Parameter_3Prod_3Com_3
Act1Par102Par201Par323
Act2Par112Par223   
Act3         

 

 

 

Hi, i want to transform above mentioned input data to Output in sas.

Kindly Help.

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please post test data in the form of a datastep, we do not need to be typing that in or opening dangerous Excel files.  As for your problem, you can simply transpose twice, once for prod once for com and merge together, or use arrays.  There is thousands of examples of this on here:

https://communities.sas.com/t5/forums/searchpage/tab/message?q=transpose+multiple+values

(From the search bar)

View solution in original post

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please post test data in the form of a datastep, we do not need to be typing that in or opening dangerous Excel files.  As for your problem, you can simply transpose twice, once for prod once for com and merge together, or use arrays.  There is thousands of examples of this on here:

https://communities.sas.com/t5/forums/searchpage/tab/message?q=transpose+multiple+values

(From the search bar)

sivastat08
Pyrite | Level 9
Thanks sir.
Yavuz
Quartz | Level 8
I tried to write this but not sure,

proc transpose data=have out=want prefix=parameter;
by account;
id parameter;
var prod;
run;

proc transpose data=have out=want1 prefix=parameter;
by account;
id parameter;
var com;
run;

Data final;
Merge want want1;
Run;
sivastat08
Pyrite | Level 9

thanks sir.

art297
Opal | Level 21

A group of us wrote a macro that accomplishes tasks exactly like the one you are trying to solve. You can download it (for free of course) at: http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

 

After running that code, the only statement you'd need to accomplish the task would be:

%transpose(data=have, out=want, by=account, Guessingrows=1000,
var=Parameter Prod Com)

 

Art, CEO, AnalystFinder.com

 

sivastat08
Pyrite | Level 9
thanks sir.
Yavuz
Quartz | Level 8
Dear art297 the transpose macro which you shared is excellent. I want to special thanks to you for it.
Ksharp
Super User

Check the MERGE skill proposed by Me,Arthur.T, Matt .

 

 

 

data have;
infile cards expandtabs truncover;
input Account $ Parameter $ Prod	Com;
cards;
Act1	Par1	0	2
Act1	Par2	0	1
Act1	Par3	2	3
Act2	Par1	1	2
Act2	Par2	2	3
Act3	 	. . . 	 
;
run;
data have;
 set have;
 by account;
 if first.account then n=0;
 n+1;
run;
proc sql noprint;
select distinct catt('have(where=(n=',n,') 
rename=(parameter=parameter_',n,' prod=prod_',n,' com=com_',n,'))')
into : merge separated by ' '
from have;
quit;
data want;
 merge &merge;
 by account;
 drop n;
run;
sivastat08
Pyrite | Level 9
Thanks the details Mr.Ksharp

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
  • 10 replies
  • 1710 views
  • 7 likes
  • 5 in conversation