DATA Step, Macro, Functions and more

create Matrix using sas code

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

create Matrix using sas code

[ Edited ]

Hi, 

I am trying to create a matrix from a summary table in sas. I would appreciate any input how I can do that using sas code. I think it can be done using proc IML but I do not have the license for it. maybe someone can share if it can be done using do loop or arrays...

I want results in dataset rather than report.. Appreciate any help..

 

Thanks in advance!

 

 Have:

age         code      mean

3              1              2.6

3              2              1

3              3              0

3              4              5.7

4              1              6

4              2              7.9

4              3              1

4              4              0

5              1              0

5              2              10

5              3              5

5              4              1

 

WANT:

 

age        code                                

                 1              2              3              4

3              2.6          1              0              5.7

4              6              7.9          1              0

5              0              10           5              1

 



 


Accepted Solutions
Solution
‎11-10-2016 04:24 PM
Contributor
Posts: 20

Re: create Matrix using sas code

As an alternative to Proc Transpose, you can try this:

 

data want;

if _n_ = 1 then do;

age = .; code1 = 1; code2 = 2; code3 = 3; code4 = 4;

output;

end;

set have(where = (code=1));

code1=mean;

set have(where=(code=2));

code2=mean;

set have(where=(code=3));

code3=mean;

set have(where=(code=4));

code4=mean;

keep age code1-code4; output;

run;

 

 

 

 

View solution in original post


All Replies
Super User
Posts: 10,500

Re: create Matrix using sas code

Do you want a data set or a report table?

One way for a report would be:

 

proc tabulate data=have;

   class age code;

   var mean;  /* Note that Naming varibles with statistics like Mean can cause confusion*/

   table age,

            code='Spread' * mean=''*mean

    ;

run;

If a dataset there will be issues as 1, 2, 3 and 4 are not valid variable names.

Occasional Contributor
Posts: 8

Re: create Matrix using sas code

[ Edited ]

Sorry I didnt mention, I want to have it as dataset so that i can export it to excel late on..

is it possible to have code as variable name in all columns...

Super User
Posts: 17,819

Re: create Matrix using sas code

Please look at proc transpose then. 

 

Look at ID and IDLABEL statements, along with prefix option to get your requirements.

PROC Star
Posts: 551

Re: create Matrix using sas code

So you do not want the IML solution? I would recomend PROC TABULATE then

Occasional Contributor
Posts: 8

Re: create Matrix using sas code

Sorry I didnt mention, I want to have it as dataset so that i can export it to excel late on..

Super User
Posts: 5,082

Re: create Matrix using sas code

I'm in the transpose camp.  Here's an untested attempt:

 

proc transpose data=have out=want (drop=_name_) prefix=code_;

var mean;

by age;

id code;

run;

 

It probably works as is, but if you have difficulty with it then tweaks would be possible.

Solution
‎11-10-2016 04:24 PM
Contributor
Posts: 20

Re: create Matrix using sas code

As an alternative to Proc Transpose, you can try this:

 

data want;

if _n_ = 1 then do;

age = .; code1 = 1; code2 = 2; code3 = 3; code4 = 4;

output;

end;

set have(where = (code=1));

code1=mean;

set have(where=(code=2));

code2=mean;

set have(where=(code=3));

code3=mean;

set have(where=(code=4));

code4=mean;

keep age code1-code4; output;

run;

 

 

 

 

Valued Guide
Posts: 797

Re: create Matrix using sas code

Here's a slightly safer solution:

 

data want (drop=code);
   merge have (where=(code=1) rename=(mean=mean1))
              have (where=(code=2) rename=(mean=mean2))
              have (where=(code=3) rename=(mean=mean3))
              have (where=(code=4) rename=(mean=mean4));
   by age;
run;

 

 

It's safer because is does not require all four code levels to be present for each age.

 

Regards,

Mark

Super User
Posts: 17,819

Re: create Matrix using sas code

Proc Transpose is the best solution in this particular problem. Less code, more dynamic. There's no need to know the values in the variable Code or even how many there are. 

 

data have;
	input age code mean;
	cards;
3              1              2.6
3              2              1
3              3              0
3              4              5.7
4              1              6
4              2              7.9
4              3              1
4              4              0
5              1              0
5              2              10
5              3              5
5              4              1
;
run;

proc sort data=have;
by age;
run; proc transpose data=have out=want prefix=C; by age; id code; idlabel code; var mean; run;
☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 352 views
  • 1 like
  • 7 in conversation