topic Re: Proc Report Help! in SAS Procedures

Proc Report Help!

KevinC_ — Sat, 13 Oct 2012 15:29:43 GMT

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

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;

Re: Proc Report Help!

KevinC_ — Sat, 13 Oct 2012 15:41:07 GMT

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

Re: Proc Report Help!

Tom — Sat, 13 Oct 2012 17:16:53 GMT

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

column id _: ;

Re: Proc Report Help!

Cynthia_sas — Sat, 13 Oct 2012 17:17:54 GMT

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

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

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;

Re: Proc Report Help!

KevinC_ — Sat, 13 Oct 2012 17:54:55 GMT

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

Re: Proc Report Help!

KevinC_ — Sun, 14 Oct 2012 15:13:40 GMT

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

Re: Proc Report Help!

KevinC_ — Sun, 14 Oct 2012 15:22:42 GMT

Never mind my last question. I found the answer:

define ID/ order = data;

Thank you !