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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.