Watch this Ask the Expert session to learn when to use dummy variables to get your desired output from PROC REPORT.
Watch the webinar
You will learn:
- When to create dummy variables for PROC REPORT.
- How to correctly incorporate dummy variables in your code.
- Points to consider when using PROC REPORT in SAS® Viya®.
The questions from the Q&A segment held at the end of the webinar are listed below. The slides and code from the webinar are attached.
Q&A
Can a sort "override" an ORDVAR type variable?
The goal of the ordvar is to set your desired sorting order. You do not need to do a PROC SORT prior to the PROC REPORT. As long as you have set the value you want correctly and place the ordvar in the appropriate location in the COLUMN statement you should get what you want.
What type of var is month? If it's character, why didn't April sort first?
Month is a SAS date variable. It is a date but has a format of month name. I knew that the sashelp.prdsale dataset is ordered January, February, March, so on. In the DEFINE statement for month, I used ORDER=DATA. But you're correct. Had I not used the ORDER= option, it would have ordered based on the formatted value and April would have been first.
What would I do if I wanted a space/break between months? Add a dummy blank column?
You can create a dummy character value that is blank. If you also include that under the ACROSS variable the empty column would be placed under January and February, and so on. If you want it to be its own separate column that doesn't fall under the January, February, March headers, you will have to restructure your data set, then you could put a blank between them. But yes, people will often create a blank dummy variable to do this. They can use it however many times they want in the COLUMN statement, X Blank Y blank Z.
Can you explain the difference between a group and an order variable? They seem to act in a very similar way.
Yes, they do behave similarly. Both the group usage and the order usage sort the data. Using a group consolidates; if I have multiple records with type equal to earthquake it is going to consolidate so I get one record – one row of type equals earthquake. Order does not do that and doesn't act as much like a class variable. If you used a CLASS statement in PROC MEANS, TABULATE or something similar, you know it'll put them all together. Order doesn't do that. If I say column age, define age as a group here, and run it, it orders it 11, 12 through 16 and consolidates it so I get one row for each value of age. If I change this to order and run it, it sorts but it does not consolidate it. I'll get 2 rows of 11. Then I'll get 5 rows of 12 and so on.
Can you export this to excel? *Export PROC Report outcomes to Excel that is?
If I take any of these examples, I can use an ODS EXCEL statement and send the report to Excel. In my first ODS EXCEL statement I use FILE= and specify my slash filename dot XLSX. At the end of whatever I want in Excel, I would have an ODS EXCEL CLOSE statement. I assume you're sending it to whichever destination you need for your purposes – Excel, HTML, PDF, RTF.
Please tell us how you get SAS to put lines on as we are writing code. How do we number the lines?
That is in the tools options. Enhanced editor if you're using the handset or on the General Tab, show line numbers.
Can you export multiple PROC Reports into different tabs of one Excel workbook?
Yes. In the ODS EXCEL statement is an option called OPTIONS=. In there you can use SHEET_INTERVAL. There are multiple papers on how to do this. If for some reason the SAS notes, documentation and other examples don’t help you to get it to work the way you want, send an email to support@sas.com and Tech Support will help you get the interval you want.
Was the first example sending the report to an Excel? How do you send your report to the body of an email, and not have to open a separate Excel document?
I would suggest using Google or web search to find papers and examples that are out there. If you can't get it to work, Tech Support will help you out.
While exporting to Excel, the $ symbol does not go through. Is there a way of showing $ when my SAS report is exported to Excel without ODS, which takes time and does not work for big data?
If you are not using ODS to export to Excel, then are you using PROC EXPORT? I think that you have to choose character vs. numeric before its exported. Meaning, change it to character and add a dollar sign before the report. That said, I am not an expert on PROC EXPORT, you might need to email support@sas.com and check with the expert.
In the PROC SUMMARY example with the monthly sales, did you have to sum sales both in the PROC SUMMARY and in the PROC REPORT?
Technically, yes. PROC SUMMARY summed the sales so that I could create the counter variable based on which product had the highest sales within year and month. I fed the summarized data into PROC REPORT. PROC REPORT did summarization in the background but had nothing to sum up because everything was already summed.
I want cells to show like ("% (CI)" or "% (n)") in a table. Do you have any examples to share? Any other webinars on Proc Report coming up?
I think what you are asking is to have a cell that has both a number and text in it. Those values must be combined into one variable. That can be done in a DATA Step prior to PROC REPORT or within PROC REPORT.
combo = put(num,4.0) || ' (' || put(pct,4.1) || ')';
For the fourth example, why "" after the across? Can you explain it again?
By default, PROC REPORT will display a variable’s label. If it does not have a permanent label the variable name is displayed. In that example I did not want a header row that had either the name of the dummy variable or its label. In the DEFINE statement I used empty quotation marks to suppress the variable name/label. I still see the value though.
Would you please let us know about the email address we can use to ask SAS questions in regard to problems in coding?
Support@sas.com
Recommended Resources
The REPORT Procedure: A Primer for the Compute Block
The SAS® Programmer's PROC REPORT Handbook: Basic to Advanced Reporting Techniques
The SAS® Programmer's PROC REPORT Handbook: ODS Companion
It’s All about the Base—Procedures
It’s All about the Base—Procedures, Part 2
Please see additional resources in the attached slide deck.
Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q&A, slides and recordings from other SAS Ask the Expert webinars.
Click image to register for webinar