topic creating columns using proc sql in sas in SAS Data Science

creating columns using proc sql in sas

Mrinal — Tue, 28 Oct 2014 08:41:51 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: creating columns using proc sql in sas

RW9 — Tue, 28 Oct 2014 09:24:47 GMT

Hi,

Well you can do it by transpose, arrays or SQL joins up to you. I don't see the value in this however, what benefit are the a_week_x and a_stage_x variables? This information is in the amount column header? I would suggest all you need is the transposed value:

data have;
length a_stage a_week $200.;
infile datalines dlm=" ";
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=have;
by a_id a_week;
run;

data have;
set have;
id_var=input(tranwrd(a_week,"wk_",""),best.);
run;

proc transpose data=work.have out=want prefix=a_amount_ ;
by a_id;
var a_amount;
id id_var;
run;

Re: creating columns using proc sql in sas

Loko — Tue, 28 Oct 2014 10:03:40 GMT

Hello,

a data step solution:

data have;
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
;
proc sort data=have;
by a_id;
run;

proc sql noprint;
select count(distinct A_week) into :cvals from have;
quit;

data want (drop=i a_stage a_amount a_week);

array A_week_{&cvals} $;
array A_Stage_{&cvals} $;
array A_Amount_ {&cvals} ;

do until(last.a_id);
set have;
by a_id;
i=sum(i,1);
A_week_{i}=A_week;
A_Stage_{i}=A_Stage;
A_Amount_{i}=A_Amount;
end;
run;