BookmarkSubscribeRSS Feed
Mrinal
Calcite | Level 5

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

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Loko
Barite | Level 11

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to choose a machine learning algorithm

Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 880 views
  • 0 likes
  • 3 in conversation