topic Re: Data transformation in SAS Programming

Data transformation

sivastat08 — Tue, 28 Feb 2017 14:34:13 GMT

Input Data:

Account	Parameter	Prod	Com
Act1	Par1	0	2
Act1	Par2	0	1
Act1	Par3	2	3
Act2	Par1	1	2
Act2	Par2	2	3
Act3

Output Format:

Account	Parameter_1	Prod_1	Com_1	Parameter_2	Prod_2	Com_2	Parameter_3	Prod_3	Com_3
Act1	Par1	0	2	Par2	0	1	Par3	2	3
Act2	Par1	1	2	Par2	2	3
Act3

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

Kindly Help.

Re: Data transformation

RW9 — Tue, 28 Feb 2017 14:48:53 GMT

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)

Re: Data transformation

Yavuz — Tue, 28 Feb 2017 15:34:56 GMT

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;

Re: Data transformation

art297 — Tue, 28 Feb 2017 15:46:10 GMT

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

Re: Data transformation

sivastat08 — Tue, 28 Feb 2017 15:51:32 GMT

thanks sir.

Re: Data transformation

sivastat08 — Tue, 28 Feb 2017 15:52:00 GMT

thanks sir.

Re: Data transformation

sivastat08 — Tue, 28 Feb 2017 15:59:45 GMT

Thanks sir.

Re: Data transformation

Yavuz — Tue, 28 Feb 2017 19:14:54 GMT

Dear art297 the transpose macro which you shared is excellent. I want to special thanks to you for it.

Re: Data transformation

Ksharp — Wed, 01 Mar 2017 11:48:56 GMT

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;

Re: Data transformation

Ksharp — Wed, 01 Mar 2017 11:50:02 GMT

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

Re: Data transformation

sivastat08 — Fri, 03 Mar 2017 07:59:33 GMT

Thanks the details Mr.Ksharp