Good afternoon,
I'd like to write some code that will prepopulate a Table 1 for an academic journal with summary data from Proc Freq, Proc Univariate, etc, but I've never seen anything I can modify for this purpose.
I assume this could be accomplished with a macro that outputs the results to Excel, but I have no idea where to start.
The table would ideally look something like this:
Variable Name | Category 1 | Categeory 2 | P-value |
Variable 1 | |||
1 | N(%) | N(%) | Chi-sq or Fisher's |
2 | N(%) | N(%) | |
3 | N(%) | N(%) | |
Variable 2 | Mean (SD) | Mean (SD) | t-test |
Median (IQR) | Median (IQR) |
Thanks in advance!
The key is going to be creating data sets from each of the procedures, combining them and then using an appropriate report procedure to create the desired table appearance.
Unless someone is paying a lot of money to make me create poor table outputs I would not combine values into a single cell, your N (%) and Mean (SD) imply that, and really really strongly object to combining different statistics in a single column with that layout. All you need is one person to misunderstand that this row is no longer N (%) and then spend lots of time explaining each and every cell in the table. Also to combine such values in a single cell you create string values which means that alignment can get very difficult to make pretty or sometimes even sensible.
Proc tabulate will actually do a chunk of that table directly from one data set but not the tests which would have to come from something else. Note that a bare p-value associated may not be very helpful without a great deal of explanation.
Consider this:
proc tabulate data=sashelp.class; class sex age; var height; table age*(n colpctn) height*(mean std median qrange ), sex ; run;
Qrange is the interquartile range.
You can certainly take the output of PROC FREQ and then use PROC REPORT to get a table like this. There may be some manipulation between the two steps needed, as the desired output that you show is very general. I'm not sure why you think you need to write a macro here, my opinion is that no macro is needed; nor do I understand why you mention Excel.
I think you need to be much more specific about what variables you have for anyone to start writing code. There is also the %TABLEN macro which might help here.
Before getting into PROC SUMMARY or REPORT to beautify a table (step 2), or outputting it to xcel (step 3), try making a crude table of the results containing just the values that you need (step 1). To do that, you would need to combine SAS output tables from PROC FREQ and UNIVAR. Both procedures have several OUTPUT options, as statements ("OUTPUT ...;") or options after another statement ("/ OUTPUT ...;"). Try experimenting with that seeing which outputs contain useful values. If those tables are not enough, you may try preceding the procedures by "ODS TRACE ON;". That will list all the names of all the output tables in the log. After you see the list, you can save any of those tables under any name you like using "ODS OUTPUT listed_table_name_from_the_log=any_name_you_like;". More information is found here: https://blogs.sas.com/content/iml/2017/01/09/ods-output-any-statistic.html
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: