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
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;
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
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: