DATA Step, Macro, Functions and more

Data transformation

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

Data transformation

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.

 


Accepted Solutions
Solution
‎02-28-2017 10:49 AM
Super User
Super User
Posts: 7,403

Re: Data transformation

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


All Replies
Solution
‎02-28-2017 10:49 AM
Super User
Super User
Posts: 7,403

Re: Data transformation

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)

Contributor
Posts: 55

Re: Data transformation

Thanks sir.
Contributor
Posts: 43

Re: Data transformation

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;
Contributor
Posts: 55

Re: Data transformation

thanks sir.

PROC Star
Posts: 7,363

Re: Data transformation

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

 

Contributor
Posts: 55

Re: Data transformation

thanks sir.
Contributor
Posts: 43

Re: Data transformation

Dear art297 the transpose macro which you shared is excellent. I want to special thanks to you for it.
Super User
Posts: 9,681

Re: Data transformation

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;
Super User
Posts: 9,681

Re: Data transformation

Contributor
Posts: 55

Re: Data transformation

Thanks the details Mr.Ksharp
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 262 views
  • 7 likes
  • 5 in conversation