How to make dataset partially transposed?

[esko1779]

As for my version of SAS, it's 7.15 (apparently).

Now let's suppose we have a dataset with the following structure:

Person ID ~~~ IsMale ~~~ IsMarried ~~~ IsCosmonaut ~~~ Metric_1 ~~~ Metric_2 ~~~ ... ~~~ Metric_n

     1                     0                   1                         0                        0.1                 0.3           ...               1

     2                     1                   0                         1                         1                     2            ...               n

      ...                   ...                 ...                        ...                         ...                    ...           ...               ...

     N                     0                   0                         1                         2.3                  3.2         ...             0.23

I'd like to transpose it in the following way:

                                                              Male                                                                  Female

                                       Married                              Single                          Married                       Single

Metrics \ Features                                           Cosm-t     Not a cosm-t                                   Cosm-t    Not a cosm-t

 sum(Metric_1)                     5                             10                   3                     6                       11                  4 

  avg(Metric_1)                     ...                              ...                 ...                      ...                       ...                 ...                

  std(Metric_2)                      ...                              ...                 ...                      ...                       ...                 ...         

        ....                                 ...                              ...                 ...                      ...                       ...                 ...         

  sum(Metric_n)                    ...                              ...                 ...                      ...                       ...                 ...         

  min(Metric_n)                     ...                              ...                 ...                      ...                       ...                 ...     

I hope this was understandable. Let me create a dataset for test:

data have; 
 infile datalines missover; 
 input IDnumber $ IsMale IsMarried IsCosmonaut Age Height Weight; 
 datalines; 
1 0 0 0 10 140 35
2 0 0 1 30 180 65
3 0 1 0 40 170 80
4 0 1 1 25 184 70
5 1 0 0 13 150 41
6 1 0 1 23 179 55
7 1 1 0 80 160 50
8 1 1 1 28 175 62
; 

My idea is to create proc report, set columns to final metrics (sum(Age), avg(Height) etc.) and group all by IsMale, IsMarried and IsCosmonaut, after that transpose it. But I'm not sure that I will be able to transpose proc report table. Is there another way or my idea works as well? Or maybe I should use proc tabulate or smth else for this goal? If there exists more pretty way to illustrate this kind of information, I will be happy to use it! Thank you in advance! 

[ballardw · Accepted solution]

---

@esko1779 wrote:

As for my version of SAS, it's 7.15 (apparently).

Now let's suppose we have a dataset with the following structure:

Person ID ~~~ IsMale ~~~ IsMarried ~~~ IsCosmonaut ~~~ Metric_1 ~~~ Metric_2 ~~~ ... ~~~ Metric_n

     1                     0                   1                         0                        0.1                 0.3           ...               1

     2                     1                   0                         1                         1                     2            ...               n

      ...                   ...                 ...                        ...                         ...                    ...           ...               ...

     N                     0                   0                         1                         2.3                  3.2         ...             0.23

I'd like to transpose it in the following way:

                                                              Male                                                                  Female

                                       Married                              Single                          Married                       Single

Metrics \ Features                                           Cosm-t     Not a cosm-t                                   Cosm-t    Not a cosm-t

 sum(Metric_1)                     5                             10                   3                     6                       11                  4 

  avg(Metric_1)                     ...                              ...                 ...                      ...                       ...                 ...                

  std(Metric_2)                      ...                              ...                 ...                      ...                       ...                 ...         

        ....                                 ...                              ...                 ...                      ...                       ...                 ...         

  sum(Metric_n)                    ...                              ...                 ...                      ...                       ...                 ...         

  min(Metric_n)                     ...                              ...                 ...                      ...                       ...                 ...     

I hope this was understandable. Let me create a dataset for test:

data have; 
 infile datalines missover; 
 input IDnumber $ IsMale IsMarried IsCosmonaut Age Height Weight; 
 datalines; 
1 0 0 0 10 140 35
2 0 0 1 30 180 65
3 0 1 0 40 170 80
4 0 1 1 25 184 70
5 1 0 0 13 150 41
6 1 0 1 23 179 55
7 1 1 0 80 160 50
8 1 1 1 28 175 62
; 

My idea is to create proc report, set columns to final metrics (sum(Age), avg(Height) etc.) and group all by IsMale, IsMarried and IsCosmonaut, after that transpose it. But I'm not sure that I will be able to transpose proc report table. Is there another way or my idea works as well? Or maybe I should use proc tabulate or smth else for this goal? If there exists more pretty way to illustrate this kind of information, I will be happy to use it! Thank you in advance! 

---

I would call that a summary not a transposition and the final layout you show looks like a report not a data set.

I find proc report awkward for multiple nestings as you show, but Proc tabulate does will with appropriate formats. I am guessing that the 1 level of the isXXX variables are the male, married, cosmonaut.

data have; 
 infile datalines missover; 
 input IDnumber $ IsMale IsMarried IsCosmonaut Age Height Weight; 
 datalines; 
1 0 0 0 10 140 35
2 0 0 1 30 180 65
3 0 1 0 40 170 80
4 0 1 1 25 184 70
5 1 0 0 13 150 41
6 1 0 1 23 179 55
7 1 1 0 80 160 50
8 1 1 1 28 175 62
; 
run;
proc format library=work;
value ismale
0='Female'
1='Male'
;
value ismarried
0='Single'
1='Married'
;
value iscosmonaut
0='Not cosmonaut'
1='Cosmonaut'
;

Proc tabulate data=have ;
   class ismale ismarried iscosmonaut;
   format ismale ismale. ismarried ismarried. iscosmonaut iscosmonaut.;
   var age height weight;
   table (age height weight)*(sum mean std max min),
          ismale=''*ismarried=''*Iscosmonaut=''
          /misstext=' ' ;
run;

The example data did not provide any variability so STD is going to be missing for that.

Most of the common statistics and some interesting percentages are available in Proc Tabulate (once you figure them out) as well.

[esko1779]

Great thanks for your answer! It's the construction that I really need. However, there is a difference between your table and the table I try to construct:
1) as you can notice, I wanted to divide only single persons to cosmonauts/non-cosmonauts (let's just assume that married people are not likely to be cosmonauts), so I don't need this partition for male&married or for female&married
2) how could I display just my custom metrics in the first column? I.e. not a coulmn
Age Sum
Mean
Height Sum
Max
...
but
f(Age)
g(Age)
2*Height
...
And could I name this metrics further in report (decode short name of this metrics, to say, in Russian language; because while using proc sql I can't give Russian names to columns)?
I will try to play around this code in order to achieve desirable view and add a reply in one-two days.

__________

FIRST EDIT:

After several hours of failed attempts I finally have dealt with the first part!

Here's the slightly modified code:

proc format;

value skip low-high=[8.] .=' ';

run;

data have_upd;
set work.have;

if ismarried=1 then
iscosmonaut=.;

format IsCosmonaut skip.;
run;

proc tabulate data=have_upd;
class ismale ismarried iscosmonaut / missing;
format ismale ismale. ismarried ismarried. iscosmonaut iscosmonaut.;
var age height weight;
table (age height weight)*(N),
ismale=''*ismarried=''*Iscosmonaut=''
/misstext=' ' ;
run;

[ballardw]

---

@esko1779 wrote:

Great thanks for your answer! It's the construction that I really need. However, there is a difference between your table and the table I try to construct:
1) as you can notice, I wanted to divide only single persons to cosmonauts/non-cosmonauts (let's just assume that married people are not likely to be cosmonauts), so I don't need this partition for male&married or for female&married

---

Not really. I don't see any description in the text that said "only single persons" and your data included married with cosmonaut values. Likely with something like that I might presummarize the data by groups and then set the counts/ etc. as missing.

but
f(Age)
g(Age)
2*Height

I am not sure what that is supposed to mean. Your initial post only showed a description of metric and statistic. The Row label next can be suppressed for a variable or statistic by use of the variable name or statistic followed by a =' ' such as sum=' ' would remove the text SUM. If you want different labels for combinations of variable names you may not want to use the short hand (var1 var2)*(statistic1 statistic2) but list them individually such as

Age=' ' *(sum= 'sum(Age)' mean='mean(Age)' )

Height=' '*(mean= 'm(Height)' std='Deviation(H)')

or any text you place in side the quotes.

When you start suppressing the labels you may want to add the table option row=float after the misstext.

You can also  group columns some what.

Compare what happens with

ismale=''* (ismarried='' Iscosmonaut='') intead. The * is a NEST operator reporting levels within, the () creates a group

[esko1779]

Thanks again! I haven't fixed some of my problems yet, but I'm gonna finish it by myself, so I accept your first post that helped me a lot as a solution. Good luck!

[Vince_SAS]

---

@esko1779 wrote:

As for my version of SAS, it's 7.15 (apparently). 

---

It is likely that your version of SAS Enterprise Guide is 7.15.

Run this code to display in the log information about your version of SAS:

%put The short SAS version number is: &SYSVER;

%put The long SAS version number is: &SYSVLONG;

%put The short operating system name where SAS is runing is: &SYSSCP;

%put The long operating system name where SAS is runing is: &SYSSCPL;

%put The detailed operating system information where SAS is running is: &SYSHOSTINFOLONG;

Vince DelGobbo

SAS R&D

[esko1779]

Oh, thanks! It's 9.4.