topic Re: Using proc format to order table variables in SAS Programming

Using proc format to order table variables

myaarylon — Mon, 18 Nov 2024 00:24:51 GMT

Hi, I've been trying to get this 2x2 table (generated through proc freq) sorted so it shows 1 in the first column + row, and 0 in the second column + row. Even specifying "order=formatted", it still displays 0 in the first column + row which messes up some of the calculated stats. Any ideas what is going wrong here? Thank you!

proc format;
value noyes 1="Yes" 0="No" ;
run;

proc freq data=mydata.study order=formatted;
ods exclude riskdiffcol2;
tables ins*m1/nocol nopercent nocum riskdiff chisq;
format ins m1 noyes.;
run;

Re: Using proc format to order table variables

quickbluefish — Mon, 18 Nov 2024 00:50:26 GMT

It's sorting based on the format, as you specified - Yes sorts after No alphabetically.

Re: Using proc format to order table variables

Tom — Mon, 18 Nov 2024 00:51:24 GMT

Can you clarify why it matters what order the values appear in the table?

If the goal is just to print a 2x2 table why not use PROC FREQ to get the counts and then use PROC PRINT or PROC REPORT to make the printout?

You could convert your boolean 0=No 1=Yes values into 1=Yes 2=No values instead by subtracting from 2.

Example:

data have;
  input a b count;
  x=2-a;
  y=2-b;
cards;
1 1 1
1 0 2
0 1 3
0 0 4
;

proc format ;
  value yn 0='No' 1='Yes';
  value ny 2='No' 1='Yes';
run;

proc freq data=have ;
  ods exclude riskdiffcol2;
  tables a*b/nocol nopercent nocum riskdiff chisq;
  weight count;
  format a b yn.;
run;

proc freq data=have ;
  ods exclude riskdiffcol2;
  tables x*y/nocol nopercent nocum riskdiff chisq;
  weight count;
  format x y ny.;
run;

Same counts, same labels, reversed order.

Table of a by b

a         b

Frequency|
Row Pct  |No      |Yes     |  Total
---------+--------+--------+
No       |      4 |      3 |      7
         |  57.14 |  42.86 |
---------+--------+--------+
Yes      |      2 |      1 |      3
         |  66.67 |  33.33 |
---------+--------+--------+
Total           6        4       10


Table of x by y

x         y

Frequency|
Row Pct  |Yes     |No      |  Total
---------+--------+--------+
Yes      |      1 |      2 |      3
         |  33.33 |  66.67 |
---------+--------+--------+
No       |      3 |      4 |      7
         |  42.86 |  57.14 |
---------+--------+--------+
Total           4        6       10

Re: Using proc format to order table variables

Patrick — Mon, 18 Nov 2024 01:00:19 GMT

With order=formatted SAS will print the results based on sort order of the formatted values - No sorts before Yes.

You could use a lowercase n for no or add a leading blank to Yes to get the desired order in your report.

libname mydata "%sysfunc(pathname(work))";

data mydata.study;
  do ins=0 to 1;
    do m1=0 to 1;
      output;
    end;
  end;
run;

proc format;
  value noyes 1=" Yes" 0="No";
run;

proc freq data=mydata.study order=formatted;
  ods exclude riskdiffcol2;
  tables ins*m1/nocol nopercent nocum riskdiff chisq;
  format ins m1 noyes.;
run;

Re: Using proc format to order table variables

quickbluefish — Mon, 18 Nov 2024 01:05:56 GMT

By the way, if you're looking for the typical E+/E-, D+/D- (exposure status, disease status) as is common in epidemiology -- you can do that with these formats (and using order=formatted as you were). Keeping things as 0/1 is much more handy for other calculations.

proc format;
value fexp
    0='E-'
    1='E+'
    ;
value fdis
    0='D-'
    1='D+'
    ;
run;

proc freq data=test order=formatted;
format exp fexp. dis fdis.;
table exp*dis / missing norow nocol nopercent nocum chisq;
run;

Re: Using proc format to order table variables

Ksharp — Mon, 18 Nov 2024 01:32:49 GMT

Patrick already gave you the right solution.

Here is alternative way by using PROC SORT + ORDER=DATA .But you need make sure there are always have 0 and 1 in each row and column variable.

data study;
  do ins=0 to 1;
    do m1=0 to 1;
      output;
    end;
  end;
run;

proc format;
  value noyes 1="Yes" 0="No";
run;
proc sort data=study;by descending ins descending m1 ;run;
proc freq data=study order=data;
  ods exclude riskdiffcol2;
  tables ins*m1/nocol nopercent nocum riskdiff chisq;
  format ins m1 noyes.;
run;

Re: Using proc format to order table variables

myaarylon — Mon, 18 Nov 2024 21:48:13 GMT

This worked, thank you!!

Re: Using proc format to order table variables

myaarylon — Mon, 18 Nov 2024 21:51:40 GMT

I needed them in a certain order to calculate the correct riskdiff! thanks