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.
Here is a simple (probably not most efficient) way:
data in;
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 = in;by a_id;
proc transpose data = in out = want(drop = _name_) prefix = A_stage;
by a_id;
/*id a_stage;*/
var a_stage;
run;
proc transpose data = in out = want2(drop = _name_) prefix = A_week;
by a_id;
/*id a_week;*/
var a_week;
run;
proc transpose data = in out = want3(drop = _name_) prefix = A_amount;
by a_id;
/*id a_amount;*/
var a_amount;
run;
data really_want;
merge want:;
by a_id;
run;
Is this what you had in mind?
Best of luck,
Anca.
Hi.
Here is a simple (probably not most efficient) way:
data in;
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 = in;by a_id;
proc transpose data = in out = want(drop = _name_) prefix = A_stage;
by a_id;
/*id a_stage;*/
var a_stage;
run;
proc transpose data = in out = want2(drop = _name_) prefix = A_week;
by a_id;
/*id a_week;*/
var a_week;
run;
proc transpose data = in out = want3(drop = _name_) prefix = A_amount;
by a_id;
/*id a_amount;*/
var a_amount;
run;
data really_want;
merge want:;
by a_id;
run;
Is this what you had in mind?
Best of luck,
Anca.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.