Hi,
I have a SAS dataset which contains data in the following pattern:
A_id | A_Amount | A_Stage | A_week |
1 | 2123 | first | wk_1 |
2 | 234324 | first | wk_1 |
3 | 232 | first | wk_1 |
4 | 1 | first | wk_1 |
5 | 1123 | first | wk_1 |
1 | 3242 | second | wk_2 |
2 | 324255 | second | wk_2 |
3 | 2342 | second | wk_2 |
4 | 4324 | second | wk_2 |
1 | 242424 | third | wk_3 |
2 | 43242 | third | wk_3 |
3 | 424 | third | wk_3 |
4 | 242 | third | wk_3 |
1 | 4 | fourth | wk_4 |
2 | 243 | fourth | wk_4 |
3 | 2 | fourth | wk_4 |
I want the following view:
A_id | A_week_1 | A_Stage_1 | A_Amount_1 | A_Week_2 | A_Stage_2 | A_Amount_2 | A_week_3 | A_Stage_3 | A_Amount_3 | A_week_4 | A_Stage_4 | A_Amount_4 |
1 | wk_1 | first | 2123 | wk_2 | second | 3242 | wk_3 | third | 242424 | wk_4 | fourth | 4 |
2 | wk_1 | first | 234324 | wk_2 | second | 324255 | wk_3 | third | 43242 | wk_4 | fourth | 243 |
Can you kindly provide me the proc sql code or data set step for this.
Thanks in advance!
Best Regards,
Mrinal
Hi,
Well you can do it by transpose, arrays or SQL joins up to you. I don't see the value in this however, what benefit are the a_week_x and a_stage_x variables? This information is in the amount column header? I would suggest all you need is the transposed value:
data have;
length a_stage a_week $200.;
infile datalines dlm=" ";
input A_id A_Amount A_Stage $ A_week $;
datalines;
1 2123 first wk_1
2 234324 first wk_1
3 232 first wk_1
4 1 first wk_1
5 1123 first wk_1
1 3242 second wk_2
2 324255 second wk_2
3 2342 second wk_2
4 4324 second wk_2
1 242424 third wk_3
2 43242 third wk_3
3 424 third wk_3
4 242 third wk_3
1 4 fourth wk_4
2 243 fourth wk_4
3 2 fourth wk_4
;
run;
proc sort data=have;
by a_id a_week;
run;
data have;
set have;
id_var=input(tranwrd(a_week,"wk_",""),best.);
run;
proc transpose data=work.have out=want prefix=a_amount_ ;
by a_id;
var a_amount;
id id_var;
run;
Hello,
a data step solution:
data have;
input A_id A_Amount A_Stage $ A_week $ ;
datalines;
1 2123 first wk_1
2 234324 first wk_1
3 232 first wk_1
4 1 first wk_1
5 1123 first wk_1
1 3242 second wk_2
2 324255 second wk_2
3 2342 second wk_2
4 4324 second wk_2
1 242424 third wk_3
2 43242 third wk_3
3 424 third wk_3
4 242 third wk_3
1 4 fourth wk_4
2 243 fourth wk_4
3 2 fourth wk_4
;
proc sort data=have;
by a_id;
run;
proc sql noprint;
select count(distinct A_week) into :cvals from have;
quit;
data want (drop=i a_stage a_amount a_week);
array A_week_{&cvals} $;
array A_Stage_{&cvals} $;
array A_Amount_ {&cvals} ;
do until(last.a_id);
set have;
by a_id;
i=sum(i,1);
A_week_{i}=A_week;
A_Stage_{i}=A_Stage;
A_Amount_{i}=A_Amount;
end;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.