topic Automatic Proc Report and missing columns in SAS Procedures

Automatic Proc Report and missing columns

MrTh — Mon, 29 May 2017 10:14:18 GMT

Hi all

I have a small query that some of you might have already encoutered re: Proc report.

I have a report the get generated weekly automatically. Pb is that last week, the data was a bit different and 1 category ('Female' wasn't present in the file. That was normal but I hadn't cater for it in teh automatic report and an error was generated (see error below).

Usual in-file 'rep0' format = _NAME_ male female

This week 'rep0' format = _NAME_ male

I was thinking of going the roundabout way of creating dummy cols of '0' whenever a category was missing - but there might be a cleverer idea out there 🙂

Thanks

Cheers

proc report data = rep0 nowd style(header)=[font_size=2] missing ;
columns ("Excellent title" _NAME_ maley female) ;
define _NAME_ / ' ' left style(column)=[cellwidth=5.0cm] display ;
define male/ 'Males' format=10.0 center style(column)=[cellwidth=5.5cm] display ;
define female/ 'Females' format=10.0 center style(column)=[cellwidth=5.5cm] display ; run ; quit ;
ERROR: Variable female is not on file WORK.REP0.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Re: Automatic Proc Report and missing columns

Shmuel — Mon, 29 May 2017 11:00:46 GMT

Force having both genders in dataset before running proc report.

Assuming gender is one character then:

data rep0;
 set rep0;
      length male female $1; /* adapt length to origin */
run;

Re: Automatic Proc Report and missing columns

MrTh — Mon, 29 May 2017 11:08:04 GMT

Thanks #Shmuel - that's the option I went for.

Cheers

Re: Automatic Proc Report and missing columns

Cynthia_sas — Mon, 29 May 2017 15:44:08 GMT

Hi:

For the sake of completeness, when you are using PROC REPORT, you have 2 options inside PROC REPORT that can help you with this scenario. By default, as shown in Report #1, when I ask PROC REPORT to show me the count of students, for each SEX within each AGE, there is no F row for Age 16 because the data does not have a female for age 16.

But, as shown in Report #2, if I add the COMPLETEROWS option, then PROC REPORT "completes" all the rows the same, based on the values it finds in the data. So, with the COMPLETEROWS option added, there is now a row for F under Age 16 with a count of 0.

But what if I want to see rows for ages 17-20 because I know that for some data files, I will have those ages, but for other data files, I might not have those ages. One way is to "seed" a copy of the data will all the possible values to show on the report. Another option that works for PROC REPORT, TABULATE and a few others is the PRELOADFMT option, which allows you to specify a format that contains the combinations you want to see on the report. Report #3 shows the use of PRELOADFMT with COMPLETEROWS to get ages 17-20 with values for F and M for each age.

In some circumstances, you might prefer to seed the data to contain all combinations for variables, this is a technique that I often use when I need to put a category value on a graph, but the data does not have that value.

cynthia

Re: Automatic Proc Report and missing columns

MrTh — Mon, 29 May 2017 15:57:04 GMT

Hi Cynthia

I really appreciathe the compleness of your reply - the PRELOADFMT sounds a very good alternative that I will try. Many thanks!!