The SAS Output Delivery System and reporting techniques

variables with the same format

Reply
Occasional Contributor
Posts: 12

variables with the same format

Hello all:

  I need to display statistics of several categorical variable in the same table. something like

                                  case           control

Age         <64              5                    10

                unknown      1                     1

Gender   female         3                     6

                unknown      2                     2

The problem I run to is for each variable, there will be a line of unknown and it has to be the last line of that category. However, SAS has some strange way to order the data. For example, the code at the end will generate sth like

       

Age         <64                                

                unknown    

Gender   unknown        

                female   

The different 'unknown' format has different numerical values that should be sorted correctly. I also explicitly ask to order the table by value or 'unformatted'. SAS still seems to order the display according to format (Otherwise, the unknown for gender with value 2.1 should be displayed below female which equals 2.0). Is there any way to solve this? Why SAS orders tabulate in such a weired way?

Thanks,

data test1;

input vname options group count;

datalines;

1 1.0 1 15

1 1.0 2 12

1 1.1 1 2

1 1.1 2 25

2 2.0 1 10

2 2.0 2 11

2 2.1 1 23

2 2.1 2 20

;

proc format;

value vname_f 1='age' 2='gender';

value noptions_f 1.0='<65' 1.1='unknown' 2.0='Female' 2.1='unknown';

value group_f 1='case' 2='control';

run;

proc tabulate data=test1 order=unformatted;

var count;

class vname options group ;

table vname*options, group * count;

format vname vname_f. options noptions_f. group group_f.;

run;

Super Contributor
Posts: 349

Re: variables with the same format

Hi,

Try this...Hope this what you want

proc tabulate data=test1 order=formatted;

var count;

class vname options group ;

table vname*options, group * count;

format vname vname_f. options noptions_f. group group_f.;

run;

Thanks,

Shiva

Occasional Contributor
Posts: 12

Re: variables with the same format

I guess I am looking for a more general solution to display rows by their unformatted values, even when different values may have the same formatted output. For exmaple, I may need a table like

Variables Options

Gender   Male       

                Female  

                Unknown

Ethnic     White/Caucasian       

                Black

                Asian

                Latin American  

                Unknown

Unknown should always be the last row for each category. Apparently you cannot display by formatted value. What I did is to assign values to variables and options. e.g

proc format;

value variable_f 1 ='Gender' 2='Ethnic';

value options_f  1.1='Male' 1.2='Female' 1.3='Unknown' 2.1='White/Caucasian'

...

2.5 = 'Unknown';

The problem I run into is since both 1.3 and 2.5 have the same label 'Unknown'. The second Unknown is always displayed on top within the second category, since it has been displayed once before. e.g

Gender   Male       

                Female  

                Unknown

Ethnic     Unknown

               White/Caucasian       

                Black

                Asian

                Latin American  

This seems to be true even when I set order=unformatted/data in proc tabulate or order=internal in proc report. The SAS document reads 'PROC TABULATE maintains for subsequent categories the order that was established by earlier categories.'

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000146763.htm#a00247...

This is very annoying. All I want is to order the data by value and replace values by their format, even when 2 values have the same format. Any suggestions?

Thanks,

Peter

Super User
Posts: 5,085

Re: variables with the same format

Peter,

There are two approaches that could work.

You went through some effort to create a single variable called VNAME.  You could "undo" that, and just use your original list of variables as the rows.  PROC TABULATE will easily let you do something like:

tables gender race, group * count;

If you assign the revamped values to your current variables (for example, make GENDER take on values of 2.0 and 2.1), then you could apply your existing format to both GENDER and RACE.

Another alternative would be to change the format so there are no overlaps.  For example:

value options_f  1.0='<65'  1.1='unknown age'  2.0='Female'  2.1='unknown gender';

I don't see another alternative, but maybe somebody else will.

Good luck.

Super User
Posts: 9,682

Re: variables with the same format

You can adjust the order by padding a blank, because blank is before any ASCII character,

You want which value be first, then pad a blank before it.

proc format;

value vname_f 1='age' 2='gender';

value noptions_f 1.0='<65' 1.1='  unknown' 2.0='Female' 2.1='unknown';

value group_f 1='case' 2='control';

run;

proc tabulate data=test1 ;

var count;

class vname options group ;

table vname*options, group * count;

format vname vname_f. options noptions_f. group group_f.;

run;

Ksharp

Ask a Question
Discussion stats
  • 4 replies
  • 300 views
  • 0 likes
  • 4 in conversation