Help using Base SAS procedures

Proc Report Help!

Accepted Solution Solved
Reply
Regular Contributor
Posts: 173
Accepted Solution

Proc Report Help!

Hello Everyone,

I am trying to create a report with numeric headers (i.e. 1 2 3 4 5 etc.) using proc report. Each number represents a work day of the month (1=1st work day of the month, notice 6 is skipped because it's a weekend).  The code below is manually updated to add a new day of data (next one would be _9) and produce a new report everyday.  The code works for that.  BUT I am trying to automate this process without manually updating the program daily.  The problem is with the column statement in proc report (high lighted in brown).  If  I pre-define all the work days of the month (_1 thru _30 or _31) proc report will generate an error "variable _XX not found".   Do I make sense?  Does anyone have any suggestions on getting around this obsticle?

I really appreciate any input from anyone!!

Thank you Smiley Happy

DATA:

ID    _1  _2   _3  _4  _5  _7  _8

AA  45  78   44  09  74   77  99

BF  20   43   65  87  32   40  48

CODE:

data new1;

set new0;

label id='ID' _1='1' _2='2' _3='3' _4='4' _5='5' _6='6' _7='7' _8='8';

run;


proc report data=new1;

column id _1-_5 _7 _8;

define id /group;

rbreak after /summarize;

compute after;

     id = 'Total';

endcomp;

run;quit;


Accepted Solutions
Solution
‎10-13-2012 01:16 PM
Super User
Super User
Posts: 6,502

Re: Proc Report Help!

Why not just use a variable list in the COLUMN statement?

column id _: ;

View solution in original post


All Replies
Regular Contributor
Posts: 173

Re: Proc Report Help!

let me make myself a little more clear about what I am trying to achive: How do I pre-define all the work days of the month (_1 thru _31) in the COLUMN statement in proc report ahead of time ?  Or is there a way to include only the non-future days in the COLUMN statement and automatically add a new work day everyday?

Help please and Thank you again!!

SAS Super FREQ
Posts: 8,743

Re: Proc Report Help!

Hi:

  There is nothing you can do in PROC REPORT to have a dynamic number of variables in the COLUMN statement unless

1) you can figure out a variable list method that will work from day to day

or

2) restructure your data so that the "day" variable value (1, 2, 3, etc) appear automatically because your DAY variable can be an ACROSS item

or

3) try to "macro-ize" your code.

  I am not sure which method will work for you. Your variable names look like they came from PROC TRANSPOSE and, if that is the case, then the ACROSS method might work for you without needing a TRANSPOSE. Consider the following example (using differently structured data).

  I made 2 separate datasets -- one for 5 days and one for 8 days. With the data in the form of 1 obs per day and a DAY variable, then ACROSS will work for you and you don't have the issue that you posed.

  If your data is NOT coming from TRANSPOSE, then your choices are the original 3 choices above.

cynthia

** Make some data for 5 days and 8 days;
** in order to have 2 datasets for testing;
data five_days
     eight_days;
   length idvar $5;
   infile datalines;
   input idvar day amt;
   if day le 5 then output five_days eight_days;
   else if day gt 6 then output eight_days;
return;
datalines;
AA 1 45  
AA 2 78  
AA 3 44 
AA 4 09 
AA 5 74  
AA 7 77 
AA 8 99
BF 1 20
BF 2 43
BF 3 65
BF 4 87
BF 5 32
BF 7 40
BF 8 48
;
run;
   
ods html file='c:\temp\across_method.html';
  proc report data=five_days nowd split='_';
  title '1a) Five Days';
  column IDvar amt,day amt=Tot;
  define IDvar / group;
  define day / across;
  define amt / sum ' ';
  define Tot / sum 'Total';
  rbreak after / summarize;
  compute after;
    idvar = 'Total';
  endcomp;
run;
     
proc report data=eight_days nowd split='_';
  title '1b) Eight Days';
  column IDvar amt,day amt=Tot;
  define IDvar / group;
  define day / across;
  define amt / sum ' ';
  define Tot / sum 'Total';
  rbreak after / summarize;
  compute after;
    idvar = 'Total';
  endcomp;
run;
ods html close;


diff_num_days.png
Regular Contributor
Posts: 173

Re: Proc Report Help!

WOW, Cynthia & Tom!!  Thank you both for your suggestions!  They both worked!!

Cynthia,  Thank you very much for your explaination, detailed input, and the attached test data!  Your inputs are greatly appreciated.

Thank you both again!!!  Smiley Happy Smiley Happy

Regular Contributor
Posts: 173

Re: Proc Report Help!

Hello Guys,

I have one more question.  Is there a way for proc report NOT to sort the data by row?   I would like the report to be in the same order as below (notice ID is not in any particular alphabetical order) .

DATA:

ID    _1  _2   _3  _4  _5  _7  _8

QE  45  78   44  09  74   77  99

BF  20   43   65  87  32   40  48

ER  33  55   66   77  88   66  88

Thank you again !!

Regular Contributor
Posts: 173

Re: Proc Report Help!

Never mind my last question.  I found the answer:

define ID/ order = data;

Thank you ! Smiley Happy

Solution
‎10-13-2012 01:16 PM
Super User
Super User
Posts: 6,502

Re: Proc Report Help!

Why not just use a variable list in the COLUMN statement?

column id _: ;

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 318 views
  • 3 likes
  • 3 in conversation