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

creating columns using proc sql in sas

Reply
Occasional Contributor
Posts: 8

creating columns using proc sql in sas

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

Super User
Super User
Posts: 7,961

Re: creating columns using proc sql in sas

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;

Super Contributor
Posts: 308

Re: creating columns using proc sql in sas

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;

Ask a Question
Discussion stats
  • 2 replies
  • 228 views
  • 0 likes
  • 3 in conversation