Help using Base SAS procedures

Automatic Proc Report and missing columns

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Automatic Proc Report and missing columns

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


Accepted Solutions
Solution
a month ago
Super User
Posts: 1,165

Re: Automatic Proc Report and missing columns

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;

View solution in original post


All Replies
Solution
a month ago
Super User
Posts: 1,165

Re: Automatic Proc Report and missing columns

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;
Occasional Contributor
Posts: 8

Re: Automatic Proc Report and missing columns

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

Cheers

SAS Super FREQ
Posts: 8,687

Re: Automatic Proc Report and missing columns

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.

 

use_completerows.png

 

  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.

completerows_preloadfmt.png

 

 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

Occasional Contributor
Posts: 8

Re: Automatic Proc Report and missing columns

Hi Cynthia

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 102 views
  • 2 likes
  • 3 in conversation