Creating single variable from multiple variable and putting variable as category

[Akter]

Dear Connection,

I want to create a single variable from Multiple variable and make those multiple variable as category of the single variable and calculate Total, n and % for each category. I will have to create 5 or more such single variable from 25 variable (for multiple response). I'm not sure if its possible, If it is possible it would save my lots our Data team time. See the data below as Have and output table 'Output Want' in the attached file. I really appreciate time and effort. Male=1 and Female=2

Data have;

ID    Gender QA_1 QA_2 QA_3 QA_4 QB_1 QB_2 QB_3 QB_4
001   1          1   0   0   0   1   1   0   1
002   2          1   1   0   0   0   1   1   0
003   1          0   0   1   0   1   1   0  1
004   2          0   1   0   1   1   0   1  0
005  1          1   0   1   0   1   1   0   0

Output want is in the attached file. 

[ballardw]

---

@Akter wrote:

Dear Connection,

I want to create a single variable from Multiple variable and make those multiple variable as category of the single variable and calculate Total, n and % for each category. I will have to create 5 or more such single variable from 25 variable (for multiple response). I'm not sure if its possible, If it is possible it would save my lots our Data team time. See the data below as Have and output table 'Output Want' in the attached file. I really appreciate time and effort. Male=1 and Female=2

Data have;

ID    Gender QA_1 QA_2 QA_3 QA_4 QB_1 QB_2 QB_3 QB_4
001   1          1   0   0   0   1   1   0   1
002   2          1   1   0   0   0   1   1   0
003   1          0   0   1   0   1   1   0  1
004   2          0   1   0   1   1   0   1  0
005  1          1   0   1   0   1   1   0   0

Output want is in the attached file. 

---

Here is one way to build such a report.

Data have;
   input ID $ 	Gender 	QA_1	QA_2	QA_3	QA_4	QB_1	QB_2	QB_3	QB_4;
datalines;
001	1	1	0	0	0	1	1	0	1
002	2	1	1	0	0	0	1	1	0
003	1	0	0	1	0	1	1	0	1
004	2	0	1	0	1	1	0	1	0
005	1	1	0	1	0	1	1	0	0
;

proc format;
value gender
1='Male'
2='Female'
;
run;

proc tabulate data=have;
   class gender;
   format gender gender.;
   var qa_1-qa_4 qb_1-qb_4;
   table qa_1-qa_4,
         (all='All' gender=' ')*(n='All' sum='n'*f=best5. pctsum='%'*f=5.1)
         /box='QA'
         ;
   table qb_1-qb_4,
         (all='All' gender=' ')*(n='All' sum='n'*f=best5. pctsum='%'*f=5.1)
         /box='QB'
         ;
run;

Proc tabulate has the ability to build a number of tables with the same variables. To get an automatic 'header' like your QA and QB would require a fair amount of work reshaping your data. Two (or more) separate sections in tabulate are relatively easy.

If you have other demographics for your report you could either add additional table statements or extend the (all='All' gender=' ') to (all='All' gender=' ' ethnicity=' ') for example. You would need to add the demographic variables in a class statement and likely create other custom formats if you need to display different text from numeric values.

The gender=' ' is overriding the default appearance of the variable name or label with a blank. The *f= after the statistic is providing a specific format for the results. Most of the statistics other than N will use 2 or 4 decimals by default and wouldn't match your desired appearance. You can also use custom formats with numeric ranges if desired for statistics.

[Akter]

Great! Can i bring these two or more tables in a single page in word or export it in excel in a single sheet one below another. I tried but these are coming in different page and different sheet in Excel. I'm using SAS Studio. Thank you for your time.

[ballardw · Accepted solution]

---

@Akter wrote:
Great! Can i bring these two or more tables in a single page in word or export it in excel in a single sheet one below another. I tried but these are coming in different page and different sheet in Excel. I'm using SAS Studio. Thank you for your time.

---

The ODS EXCEL by default will create each table on a separate sheet. The SHEET_INTERVAL option controls that behavior.

(dummy code)

ods excel file="something.xlsx" options(sheet_interval='Proc');

would send each procedure's output to separate sheet. Other options are None, everything to one sheet, Page - sheet per procedure page of output which can vary a lot between procedures and options, BYGROUP each level of a by group goes to sheet when a procedure uses a BY statement, and the default TABLE.

You can change this behavior before different groups of output.

ods excel file="something.xlsx" options(sheet_interval='Proc');

< a couple of procs generating output>

ods excel options(sheet_interval='BYGROUP');

<one or more procedures using By statements>

ods excel options(sheet_interval='None');

<one or more procedures that you want all the output on one sheet>

ods excel options(sheet_interval='Table');

<one or more procedures that want each table on a separate sheet>

Ods excel close;

There are interactions with options like Sheet_label  and Sheet_name as to what the sheet tab will look like.

In Word, RTF and PDF you will get some interaction with the Pagesize settings that may split large tables and STARTPAGE ods options for controlling where new pages start in relation to tables. Not the same options as Excel so look them up.

[Akter]

Hi ballardw,
Thank you so much for your help. I really appreciate your time and patience. Its working. I'm supper happy!
You are star!
Thanks! 😊