topic Re: Crosstabulation of sums in ODS and Base Reporting

Crosstabulation of sums

Davanden — Tue, 21 Jun 2016 19:19:02 GMT

I have a database of households. C1-C6 represent attributes that a household may or may not have. They are not mutually exclusive. R1-R10 represent a different set of attributes, again not mutually exclusive. This is survey data, and the records have weights W. I want to output a table with C1-C6 on the columns and R1-R10 in the rows, where each cell is the weighted count of households that have that combination of attributes Rx and Cx. I want to avoid having "yes" and "no" columns for each Cx, and similarly for the rows.

I can recode the data any way I want, and I can use whatever Base SAS procedure(s) (9.3) I like.

Is there a relatively straight-forward way to do this? I feel like there should be, and I'm just not seeing it.

--Dav

Re: Crosstabulation of sums

Astounding — Tue, 21 Jun 2016 19:36:29 GMT

I think this does the job ... see if it matches your expectations.

proc tabulate data=have;

class r1-r10 c1-c6;

var weightvar;

tables r1-r10, (c1-c6) * weightvar * sum=' ';

run;

Re: Crosstabulation of sums

Davanden — Wed, 22 Jun 2016 14:04:48 GMT

Thank you for your help. That doesn't do what I want. Defining the c and r variables as 0/1 flags, the code produces a table that has columns for 0 and 1 values for each c, and rows for 0 and one for each r (see picture). What I want is the "1" columns and rows without the "0"s.

--Dav

Re: Crosstabulation of sums

Astounding — Wed, 22 Jun 2016 14:11:34 GMT

To some extent you can get around this. But the table isn't necessarily pretty since it doesn't eliminate the "1" labeling.

Modify the PROC TABULATE to create an output data set. Off the top of my head (needs to be verified ... getting very thin up there these days), the code would be:

proc tabulate data=have noprint;

class r1-r10 c1-c6;

var weightvar;

tables r1-r10, (c1-c6) * weightvar * sum=' ' / out=tabulate_results;

run;

Then subset those results before printing with another PROC TABULATE:

data want;

set tabulate_results;

array nums {16} c1-c6 r1-r10;

do _n_=1 to 16;

if nums{_n_}=0 then delete;

end;

run;

proc tabulate data=want;

class r1-r10 c1-c6;

var weightvar_sum;

tables r1-r10, (c1-c6) * weightvar_sum * sum=' ';

run;

The output data set automatically creates WEIGHTVAR_SUM, given that you started with an analysis variable WEIGHTVAR and requested the SUM.

Once you have created the data set WANT, there may be better ways to print your final report.

Re: Crosstabulation of sums

Davanden — Wed, 22 Jun 2016 19:24:59 GMT

Thank you again for your help. I was hoping to do this in one step, but I see how I can do it in two. The code below shows the basic logic. It will have to be fixed up to put proper labels on the rows and columns, but I know how to do that.

--Dav

PROC TABULATE DATA=TEST out=Table15 ;
     CLASS  c1-c7 r1-r4;
     VAR  unit;
     
     TABLE
         (r1-r4),
          (c1-c7)*unit=' '*sum=' '*F=Comma7.
     ;     
RUN;

DATA outTable15 (KEEP = row Col1-Col7);
     SET Table15;

     /* keep records corresponding to a 1 in the row and column */
     IF WHICHN(1,OF c1-c7) > 0 AND WhichN(1, of r1-r4)>0;

     /* the dataset is sorted by rows.  populate output column variables */
     RETAIN Col1-Col7;
     ARRAY COL[7] col1-col7;
     Col[whichN(1,of c1-c7)] = Unit_sum;

     /* write the row to the output dataset */
     IF C7=1 THEN DO;
          Row = WhichN(1, of R1-R4); /* row identifier */
          OUTPUT;
     END;
RUN;

PROC PRINT DATA=OutTable15 NOBS;
     VAR ROW Col1-Col7;
RUN;

Row    Col1    Col2    Col3    Col4    Col5    Col6    Col7

 1     1124    6436    2069    1080    1738    2245    3963
 2     1111    5503    1213    1080    1917    2195    3629
 3     1050    6063    1916    1019    1776    2112    3670
 4     1185    5876    1366    1141    1879    2328    3922