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.
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)
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)
thanks sir.
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
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.