output multiple outputs to single table

river1 · Posted 03-15-2024 09:38 PM

Hello

I am trying to output a lot of data and present it in a spreadsheet. I am creating a table with the odds ratios, p values etc for around 23 variables. Plus I am running it several times with different outcome variables. The problem I have is that it is tedious copying the data out. With the variables that have multiple levels I can use excel to manipulate them but for the single variables it is easier to copy and paste. SAS seems to output everything with merged cells. Even when I export to a spreadsheet every section is on a separate tab. It is the same problem with the percentage being in the same cell as the frequency. Very frustrating when you need to rerun the data with fresh data. Is there a way to output everything to a single table so it can be more easily manipulated?

I've given an example of the code I am using but I also have a lot of frequency tables .

Many thanks for your time

ods excel file="C:\RESULTS_D_CROSSTAB_OUTCOME1.xlsx" options(sheet_name="Sheet1" sheet_interval='none');


/*xxxxxxxxxxxxxxxxxxxxxxxx*/
title "ALL DATA -  OUTCOME1";
/*xxxxxxxxxxxxxxxxxxxxxxxx*/
%macro iterate_variables;

%local letters  i;
%let letters = VAR1...VAR22        ;

%do i=1 %to 23;
    %let VAR1 = %scan(&letters, &i);



    title "PROC FREQ - OUTCOME1*EXP1* &VAR1 ";

    proc freq data=MYLIB.DATA1;
        tables &VAR1.*OUTCOME1*EXP1/
               relrisk plots(only)=relriskplot(stats) cmh ;
        tables OUTCOME1*EXP1 / chisq oddsratio;
    run;


    title "PROC FREQ - OUTCOME1 - &VAR1 ";
    proc freq data=MYLIB.DATA1;
        tables &VAR1.*OUTCOME1/
               relrisk plots(only)=relriskplot(stats) cmh ;
        tables OUTCOME1 / chisq oddsratio;
    run;



    title "PROC LOGISTIC - OUTCOME1 - &VAR1 ";

    proc logistic DATA=MYLIB.DATA1;
        model OUTCOME1(Event = '1')= &VAR1 / expb LINK=LOGIT; 
    run;


    title "PROC LOGISTIC - OUTCOME1 - EXP1 &VAR1 ";

    proc logistic DATA=MYLIB.DATA1;
        model OUTCOME1(Event = '1')= EXP1 &VAR1 / expb LINK=LOGIT; 
    run;

%end;

%mend iterate_variables;

%iterate_variables;

ballardw · Posted 03-16-2024 03:22 AM

I would suggest providing a small example data step similar to your data to create a data set we can test code with.

The show what you mean by "multiple outputs in a single table". Unfortunately there are actually many ways some people think of "table" and without a clear example then it is hard to make.

Typically if something is data for further manipulation I think data set, if people are going to read it then it is a report.

In many cases what I think you are possibly wanting you want to create data sets out side of your write data to Excel and combine them into a single data set and Print or what ever that combined set.

You can use ODS OUTPUT to place every displayed table that a procedure like Proc Freq or Logistic creates into a data set.

Then carefully combine them, likely of similar types. But really we need to see actual desired output for some example input data.

If you can't make a data set then look at ones used in Proc logistic examples with similar analysis that you want and try adjusting your code to use that so we have something to test. Then show or carefully describe the output you want in "single table".

river1 · Posted 03-16-2024 05:28 AM

Thank you for your reply, I'd be grateful for an example of the ODS OUTPUT and how to combine the output - it would be helpful to be able to output to excel in a format that I can manipulate the data. I've used the 'ods excel file= ...' but it outputs everything into separate tabs plus there are merged cells and the percentages output in the same cell as the frequency so it is difficult to copy and paste quickly.
My dataset has around 50 variables and 60,000 observations. Mostly binary variables and a couple of categorical variables.

I've made an example of the data and an example of one of my tables where I am pasting out the frequencies, I hope that is okay?

1. Example data:

2. Example of a table I am creating with the output

3. Example code to produce some frequency tables

ID2	ID	EXP1	OUT1	OUT2	OUT3	OUT4	VAR1	VAR2	VAR3	VAR4	VAR5	VAR6	VAR7	VAR8	VAR9	VAR10	VAR11	VAR12	VAR13	VAR14	VAR15	VAR16	VAR17	VAR18	VAR19	CITY_NAME
A1	123	0	1	0	0	1	1	1	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1	1	NEW_YORK
A2	124	0	0	1	0	1	1	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	LONDON
A3	125	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	DEHLI
A4	126	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	1	0	0	0	0	KATHMANDU
A5	127	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	1	0	0	1	0	0	0	EDINBURGH
A6	128	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	TORONTO
A7	129	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1	0	0	1	0	0	0	0	0	TOKYO
A8	130	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	1	1	0	NEW_YORK
A9	131	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	LONDON
A10	132	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	DEHLI
A11	133	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	KATHMANDU
A12	134	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	EDINBURGH
A13	135	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	1	0	TORONTO
A14	134	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	EDINBURGH
A15	135	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	1	0	TORONTO
A16	125	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	DEHLI
A17	126	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	1	0	0	0	0	KATHMANDU
A18	127	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	1	0	0	1	0	0	0	EDINBURGH
A19	128	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	TORONTO
A20	129	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1	0	0	1	0	0	0	0	0	TOKYO
A21	130	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	1	1	0	NEW_YORK
A22	131	0	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	LONDON
A23	132	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	DEHLI

	                  Label	| EXP1 (0) | % | OUT1 (0) | % | OUT1 (2) | % | EXP1 (1) | % | OUT1 (0) | % | OUT1 (1) | %|
VAR1*EXP1*COF_2	DETAILS1 																	
VAR1*EXP1*COF_2	DETAILS2																				
VAR1*EXP1*COF_2	DETAILS3																				0

proc freq data=MYLIB.DATA1;
   tables VAR1*EXP1*OUT1 / chisq outpct nocum norow nocol;
   tables VAR2*EXP1*OUT1 / chisq outpct nocum norow nocol;
   tables VAR3*EXP1*OUT1 / chisq outpct nocum norow nocol;
   tables VAR4*EXP1*OUT1 / chisq outpct nocum norow nocol;
   tables VAR5*EXP1*OUT1 / chisq outpct nocum norow nocol;
   tables VAR6*EXP1*OUT1 / chisq outpct nocum norow nocol;
   tables VAR7*EXP1*OUT1 / chisq outpct nocum norow nocol;
   tables VAR8*EXP1*OUT1 / chisq outpct nocum norow nocol;
run;

ballardw · Posted 03-16-2024 01:02 PM

A very useful tool to know about in SAS is ODS TRACE. That will show you the objects created by any procedure that does. Also, you might not be aware that Proc Freq and most procedures will allow lists of variables so you can shorten code. This runs Var1 through Var5 against the other variables.

An example of a data set in data step code so we can run this:

data example;
  input ID2 $	ID	EXP1	OUT1	OUT2	OUT3	OUT4	VAR1	VAR2	VAR3	VAR4	VAR5	VAR6	VAR7	VAR8	VAR9	VAR10	VAR11	VAR12	VAR13	VAR14	VAR15	VAR16	VAR17	VAR18	VAR19	CITY_NAME :$25.;
datalines;
A1	123	0	1	0	0	1	1	1	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1	1	NEW_YORK
A2	124	0	0	1	0	1	1	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	LONDON
A3	125	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	DEHLI
A4	126	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	1	0	0	0	0	KATHMANDU
A5	127	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	1	0	0	1	0	0	0	EDINBURGH
A6	128	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	TORONTO
A7	129	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1	0	0	1	0	0	0	0	0	TOKYO
A8	130	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	1	1	0	NEW_YORK
A9	131	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	LONDON
A10	132	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	DEHLI
A11	133	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	KATHMANDU
A12	134	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	EDINBURGH
A13	135	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	1	0	TORONTO
;

I didn't include all the lines as this just to show how to write the code.

ods trace on;
proc freq data=example;
   tables (VAR1-Var5)*EXP1*OUT1 / chisq outpct nocum norow nocol;
run;
ods trace off;

an extension uses Var1 through Var19 and Out1 through Out4

proc freq data=example;
   tables (VAR1-Var19)*EXP1*(OUT1-OUT4) / chisq outpct nocum norow nocol;
run;

This may save a considerable amount of execution time as your loops are repeatedly loading the source data set.

Now to the TRACE information which appears in the LOG. Because of the way my example set is made small it doesn't have all the output but the trace looked like this:

35
36   ods trace on;
37   proc freq data=example;
38      tables (VAR1-Var3)*EXP1*OUT1 / chisq outpct nocum norow nocol;
39   run;


Output Added:
-------------
Name:       CrossTabFreqs
Label:      Cross-Tabular Freq Table
Template:   Base.Freq.CrossTabFreqs
Path:       Freq.Table1of1.CrossTabFreqs
-------------

Output Added:
-------------
Name:       CrossTabFreqs
Label:      Cross-Tabular Freq Table
Template:   Base.Freq.CrossTabFreqs
Path:       Freq.Table2of1.CrossTabFreqs
-------------

Output Added:
-------------
Name:       CrossTabFreqs
Label:      Cross-Tabular Freq Table
Template:   Base.Freq.CrossTabFreqs
Path:       Freq.Table1of2.CrossTabFreqs
-------------

Output Added:
-------------
Name:       CrossTabFreqs
Label:      Cross-Tabular Freq Table
Template:   Base.Freq.CrossTabFreqs
Path:       Freq.Table2of2.CrossTabFreqs
-------------

Output Added:
-------------
Name:       CrossTabFreqs
Label:      Cross-Tabular Freq Table
Template:   Base.Freq.CrossTabFreqs
Path:       Freq.Table3of2.CrossTabFreqs
-------------

Output Added:
-------------
Name:       CrossTabFreqs
Label:      Cross-Tabular Freq Table
Template:   Base.Freq.CrossTabFreqs
Path:       Freq.Table1of3.CrossTabFreqs
-------------

Output Added:
-------------
Name:       CrossTabFreqs
Label:      Cross-Tabular Freq Table
Template:   Base.Freq.CrossTabFreqs
Path:       Freq.Table2of3.CrossTabFreqs
-------------
NOTE: There were 13 observations read from the data set WORK.EXAMPLE.
NOTE: PROCEDURE FREQ used (Total process time):
      real time           0.05 seconds
      cpu time            0.04 seconds


40   ods trace off;

The important part is the bit following the Name: in each output added section.

Hint: before running the trace select ONE set of variables that creates all the output so the log isn't quite so full.

Then you add the Ods output statements to the code and rerun it:

Such as

ods output 
   CrossTabFreqs = MyCrosstabs
   FishersExact  = MyFexact
   Chisq         = MyChiSq
;

   
proc freq data=example;
   tables (VAR1-Var9)*EXP1*OUT1 / chisq outpct nocum norow nocol;
run;

Depending on what you want out of the data there will be additional manipulation. Do note however that one common element is that each row of the output has labels of what the row represents.

There is also typically a _type_ variable in the frequency tables to indicate the row column or table totals depending on position of the 0 in the _type_ variable. So you should have enough information to select and rearrange the output, possibly with a report table with all of the output data in one table for each type.

So none of that macro looping to do proc freq would be needed.

Many of the other procs not so much but the ODS OUTPUT would allow you to create output data sets for each and then combine them for reporting.

Hint: quite often it is better to separate the "create data" and the actual output of report files if macros are used. That way you verify the data is correct and don't have to repeat the overhead of the analysis. And then fixing display issues doesn't require running the analysis.

river1 · Posted 03-17-2024 05:32 AM

Thank you, this is really helpful and much neater

output multiple outputs to single table

Re: output multiple outputs to single table

Re: output multiple outputs to single table

Re: output multiple outputs to single table

Re: output multiple outputs to single table