BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ArseneWenger
Fluorite | Level 6

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

 



 

1 ACCEPTED SOLUTION

Accepted Solutions
Tommywhosc
Obsidian | Level 7

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

9 REPLIES 9
ballardw
Super User

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.

ArseneWenger
Fluorite | Level 6

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

Reeza
Super User

Please look at proc transpose then. 

 

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

PeterClemmensen
Tourmaline | Level 20

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

ArseneWenger
Fluorite | Level 6

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

Astounding
PROC Star

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.

Tommywhosc
Obsidian | Level 7

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;

 

 

 

 

mkeintz
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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