BookmarkSubscribeRSS Feed
tradepeter
Calcite | Level 5

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;

4 REPLIES 4
shivas
Pyrite | Level 9

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

tradepeter
Calcite | Level 5

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

Astounding
PROC Star

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.

Ksharp
Super User

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

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
  • 4 replies
  • 1012 views
  • 0 likes
  • 4 in conversation