topic Re: Reshaping data in SAS Data Management

Reshaping data

Miracle — Fri, 28 Aug 2015 02:03:35 GMT

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

Re: Reshaping data

LinusH — Fri, 28 Aug 2015 04:59:32 GMT

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?

Re: Reshaping data

Kurt_Bremser — Fri, 28 Aug 2015 07:42:48 GMT

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;

site + 1;

run;

Re: Reshaping data

Ksharp — Fri, 28 Aug 2015 14:49:05 GMT

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

Re: Reshaping data

Miracle — Mon, 31 Aug 2015 02:31:30 GMT

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.