How to make columns in sas using proc sql

[Mrinal]

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

[AncaTilea · Accepted solution]

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.