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.
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.
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.