How to make columns in sas using proc sql

Mrinal — Tue, 28 Oct 2014 07:46:28 GMT

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

wk_1

first

2123

wk_2

second

3242

wk_3

third

242424

wk_4

fourth

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

Re: How to make columns in sas using proc sql

AncaTilea — Tue, 28 Oct 2014 11:20:37 GMT

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.

topic Re: How to make columns in sas using proc sql in SAS Data Science

How to make columns in sas using proc sql

Re: How to make columns in sas using proc sql