SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Reshaping data

Accepted Solution Solved
Reply
Regular Contributor
Posts: 228
Accepted Solution

Reshaping data

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

*/


Accepted Solutions
Solution
‎08-28-2015 03:42 AM
Super User
Posts: 7,868

Re: Reshaping data

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 5,441

Re: Reshaping data

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
Solution
‎08-28-2015 03:42 AM
Super User
Posts: 7,868

Re: Reshaping data

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,047

Re: Reshaping data

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

Regular Contributor
Posts: 228

Re: Reshaping data

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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