BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KevinC_
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

column id _: ;

View solution in original post

6 REPLIES 6
KevinC_
Fluorite | Level 6

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!!

Cynthia_sas
SAS Super FREQ

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
KevinC_
Fluorite | Level 6

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

KevinC_
Fluorite | Level 6

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 !!

KevinC_
Fluorite | Level 6

Never mind my last question.  I found the answer:

define ID/ order = data;

Thank you ! Smiley Happy

Tom
Super User Tom
Super User

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

column id _: ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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