BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Miracle
Barite | Level 11

Dear SAS community, wish you all well.


Can I please ask how do I improve my SAS code to my problem?

I have included a small partial output of how my exact data is like. I wish to reshape my data 'have' into 'want'.

My actual data 'have' in fact has hundreds of columns and rows which my current SAS coding is less elegant for having to write hundreds of role=nth row statement.


Your help is greatly appreciated and have a good day.

data have;

input F1 : F2 : comma10. F3 : comma10. F4 : comma10. F5 : comma10. F6 : comma10. F7 : comma10.;

datalines;

1 1,000 11 6 78 44 79

2 50 1 0 0 7 1,000

3 6,0000 5 55 60 20 11,110

4 9 6,500 8 8 7,000 6

;

run;

proc print noobs; run;

/*

F1       F2      F3    F4    F5      F6       F7

1     1000      11     6    78      44       79

2       50       1     0     0       7     1000

3    60000       5    55    60      20    11110

4        9    6500     8     8    7000        6

*/

data want;

  set have;

  array m{3} F2 F3 F4; array p{3} F5 F6 F7;

  do gender=1 to 3;

  site=_n_-1;

  role=1; freq=input(m(gender),best12.); output;

  role=2; freq=input(p(gender),best12.); output;

  end;

  drop F1-F7;

run;

proc sort data=want; by site role gender; run;

proc print noobs; var site role gender freq; run;

/*

site    role    gender     freq

  0       1        1       1000

  0       1        2         11

  0       1        3          6

  0       2        1         78

  0       2        2         44

  0       2        3         79

  1       1        1         50

  1       1        2          1

  1       1        3          0

  1       2        1          0

  1       2        2          7

  1       2        3       1000

  2       1        1      60000

  2       1        2          5

  2       1        3         55

  2       2        1         60

  2       2        2         20

  2       2        3      11110

  3       1        1          9

  3       1        2       6500

  3       1        3          8

  3       2        1          8

  3       2        2       7000

  3       2        3          6

*/

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

data want (keep=site role gender freq);

set have;

retain site 0;

array f {*} f2-f7;

do role = 1 to 2;

  do gender = 1 to 3;

    freq = f{(role-1) * 3 + gender};

    output;

  end;

end;

site + 1;

run;

View solution in original post

4 REPLIES 4
LinusH
Tourmaline | Level 20

How is your data generated, perhaps you could a better looking input?

And how do you decide which/how many variables are going into m() and p() respectively?

Data never sleeps
Kurt_Bremser
Super User

data want (keep=site role gender freq);

set have;

retain site 0;

array f {*} f2-f7;

do role = 1 to 2;

  do gender = 1 to 3;

    freq = f{(role-1) * 3 + gender};

    output;

  end;

end;

site + 1;

run;

Ksharp
Super User

I know Kurt have already given you an excellent solution.

I just want post some IML code just for FUN.

Code: Program

data have;
input F1 : F2 : comma10. F3 : comma10. F4 : comma10. F5 : comma10. F6 : comma10. F7 : comma10.;
datalines;
1 1,000 11 6 78 44 79
2 50 1 0 0 7 1,000
3 6,0000 5 55 60 20 11,110
4 9 6,500 8 8 7,000 6
;
run;
proc iml;
use have(drop=f1);
read all var _all_ into x;
close;
site=colvec(repeat(t(0:nrow(x)-1),1,ncol(x)));
role=colvec(repeat(t(1:2),nrow(x),ncol(x)/2));
gender=repeat(t(1:ncol(x)/2),2*nrow(x),1);
freq=colvec(x);
create want var {site role gender freq};
append;
close;
quit;

Xia Keshan

Miracle
Barite | Level 11

Linus, sorry for not explaining my problem clear enough.


However, I managed to get my desired data from code provided by both Kurt and Xia with only a slight change.


Again thank you very much.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1165 views
  • 6 likes
  • 4 in conversation