topic Re: create Matrix using sas code in SAS Programming

create Matrix using sas code

ArseneWenger — Thu, 10 Nov 2016 20:34:41 GMT

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

Re: create Matrix using sas code

ballardw — Thu, 10 Nov 2016 20:18:00 GMT

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.

Re: create Matrix using sas code

PeterClemmensen — Thu, 10 Nov 2016 20:20:24 GMT

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

Re: create Matrix using sas code

ArseneWenger — Thu, 10 Nov 2016 20:31:24 GMT

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...

Re: create Matrix using sas code

ArseneWenger — Thu, 10 Nov 2016 20:22:08 GMT

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

Re: create Matrix using sas code

Reeza — Thu, 10 Nov 2016 20:34:33 GMT

Please look at proc transpose then.

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

Re: create Matrix using sas code

Astounding — Thu, 10 Nov 2016 21:10:29 GMT

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.

Re: create Matrix using sas code

Tommywhosc — Thu, 10 Nov 2016 21:16:04 GMT

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;

Re: create Matrix using sas code

Reeza — Fri, 11 Nov 2016 01:32:29 GMT

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;

Re: create Matrix using sas code

mkeintz — Fri, 11 Nov 2016 16:57:52 GMT

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