Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

How to make columns in sas using proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

How to make columns in sas using proc sql

Hi,


I have a SAS dataset which contains data in the following pattern:

A_idA_AmountA_StageA_week
12123firstwk_1
2234324firstwk_1
3232firstwk_1
41firstwk_1
51123firstwk_1
13242secondwk_2
2324255secondwk_2
32342secondwk_2
44324secondwk_2
1242424thirdwk_3
243242thirdwk_3
3424thirdwk_3
4242thirdwk_3
14fourthwk_4
2243fourthwk_4
32fourthwk_4


I want the following view:

A_idA_week_1A_Stage_1A_Amount_1A_Week_2A_Stage_2A_Amount_2A_week_3A_Stage_3A_Amount_3A_week_4A_Stage_4A_Amount_4
1wk_1first2123wk_2second3242wk_3third242424wk_4fourth4
2wk_1first234324wk_2second324255wk_3third43242wk_4fourth243


Can you kindly provide me the proc sql code or data set step for this.


Thanks in advance!


Best Regards,

Mrinal


Accepted Solutions
Solution
‎10-28-2014 07:20 AM
Super Contributor
Posts: 543

Re: How to make columns in sas using proc sql

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.

View solution in original post


All Replies
Solution
‎10-28-2014 07:20 AM
Super Contributor
Posts: 543

Re: How to make columns in sas using proc sql

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 289 views
  • 0 likes
  • 2 in conversation