Reshaping data

[Miracle]

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

*/

[LinusH]

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?

[Kurt_Bremser · Accepted solution]

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]

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]

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.