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
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;
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.
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...
Please look at proc transpose then.
Look at ID and IDLABEL statements, along with prefix option to get your requirements.
So you do not want the IML solution? I would recomend PROC TABULATE then
Sorry I didnt mention, I want to have it as dataset so that i can export it to excel late on..
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.
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.