Help using Base SAS procedures

Proc Report

Reply
Occasional Contributor
Posts: 19

Proc Report

I used Proc Report to create a report that basically combines and compares two reports (old & new) with most of the data in the New Data section. I want to insert a line of text that is a format (description) for the values in the first column (data A and data B). See the sample below. The text I want to insert can go either on the row before each old data in col 1 or just after. Col 1 is neither a group nor order variable. Any suggestions? Thanks!

______New data__________________Old Data___________
col 1___col 2___col 3_______col 1___col 2___col 3___col 4
dataA__data____data_______data____data___data____data
[insert text here]___________data____data___data____data [this or]
_________________________data____data___data____data
_________________________data____data___data____data
[insert text here]_____________________________________[this]
dataB__data____data_______data____data___data____data
_________________________data____data___data____data
_________________________data____data___data____data
_________________________data____data___data____data
SAS Super FREQ
Posts: 8,864

Re: Proc Report

Hi:
Your illustration seems to indicate that the (insert text here) should span col1, col2 and col3 under NEW data -- so in the first place you have "insert text here" -- do you hope to have spanning text or not??? You cannot make text that spans columns such as you show underneath col1, col2 and col3 except with a LINE statement at a BREAK point. So the first "insert text here" is probably not going to work (if you want spanning) but the second "insert text here" might work. You can, of course, change the VALUE of COL1 or COL2 or COL3 individually in a COMPUTE block (such as test for whether they were blank and if so, change them to some value -- but not so that there's spanning.) Next...about the LINE statement...

You can only use a LINE statement in a COMPUTE block that meets 2 criteria:
1) the COMPUTE block must be associated with a specific LOCATION (BEFORE or AFTER)
2) the BEFORE or AFTER must be associated with a BREAK point -- either
COMPUTE BEFORE break_var;
COMPUTE AFTER break_var;
COMPUTE BEFORE; (top of report -- one time)
COMPUTE AFTER; (bottom of report -- one time)
...and in order to set a BREAK point, a variable must be a GROUP or ORDER variable.

So, the bottom line is that you can only do the text insertion you want at the place indicated:
[pre]
______New data__________________Old Data___________
col 1___col 2___col 3_______col 1___col 2___col 3___col 4
dataA__data____data_________data____data___data____data
[insert text here]__________data____data___data____data <-- Probably not insert here
____________________________data____data___data____data
____________________________data____data___data____data
[insert text here]_____________________________________<---this OK if group or ORDER
dataB__data____data__________data____data___data____data
_____________________________data____data___data____data
_____________________________data____data___data____data
_____________________________data____data___data____data
[/pre]

There's another thing that troubles me about the "data data data" you show...it's not clear whether each "chunk of report lines" is one OBSERVATION (DataA and DataB) or whether COL1, COL2 and COL3 just have no data under NEW??? For example, you show COL1, COL2 and COL3 as having only 1 row of data under "NEW" but show COL1, COL2, COL3 and COL4 as having 4 rows of data under "OLD". Only seeing "data" in each column and each row, doesn't help give an idea of what you are trying to compare or illustrate with your example.

Can you elaborate -- or possibly show some "fake" values which would make it clear whether each chunk is one observation or multiple observations??? Also useful to know would be your output destination of choice for the report -- RTF, PDF, HTML????

cynthia
Occasional Contributor
Posts: 19

Re: Proc Report

Cynthia, thank you so much for responding so quickly! I work at a bank and the report shows product codes and how accounts under each product were converted to different products over time. For example, some accounts with the FC product, Free Checking, may have been converted to PC, premium checking, or SC, student checking.

Note: I had the New and Old data backwards, it should be as is below. For the first version, the product description would need to be able to span several columns. I understand now how this could not work. So, the second version could work. The output destination is PDF.

I couldn't create the report with PReport alone, so I had to create and merge different datasets together and sort in a specific order. Since everything is already grouped and ordered I cannot use 'order' or 'group' with Product in a define statement, hence my problem. My code is below. Everything works, I just need to put the Product description somewhere other than where the product code is now, where there is enough room.

________Dec'09_______________________Mar'10___________
Product___#Accts___Balance_____Product___#Accts___Balance
FC_________8000___$100000______FC______7950____$90000
Free Checking ___________________PC_______25____$50000
________________________________SC_______25____$50000

Free Checking
FC_________8000___$100000______FC______7950____$90000
________________________________PC_______25____$50000
________________________________SC_______25____$50000

proc report data=report2 split="*" nowindows HEADSKIP HEADLINE;
by service ; /*services are Checking, Savings, Money Market*/
column service
old_product
old_TOTAL_accts
old_balance_total
old_balance_avg
old_profit_total
old_profit_avg
status /*this and rstatus were used to order and group the datasets*/
RSTATUS
cur_product
current_total_accts
current_balance_total
current_balance_avg
current_profit_total
current_profit_avg
prcnt_change ;
define service / order noprint;
define old_product / display 'Product' width=12 style(COLUMN)={font_weight=bold};
define old_total_accts / format=comma. '# of Accounts' width=10;
define old_balance_total / format=dollar16. 'Total Balance';
define old_balance_avg / format=dollar12. 'Avg. Balance' width=10;
define old_profit_total / format=dollar16. 'Total Profit';
define old_profit_avg / format=dollar12. 'Avg. Profit' width=10 ;
define status / computed display noprint ;
define rstatus / '' width=12 style(COLUMN)={font_weight=bold font_size=8pt};;
define cur_product / 'Product' width=12 style(COLUMN)={font_weight=bold};
define current_total_accts / format=comma. '# of Accounts';
define current_balance_total / format=dollar16. 'Total Balance';
define current_balance_avg / format=dollar12. 'Avg. Balance' width=10;
define current_profit_total / format=dollar16. 'Total Profit';
define current_profit_avg / format=dollar12. 'Avg. Profit' width=10;
define prcnt_change / format=percent11.2 '%Retained*Moved*Lost' ;

compute status;
if status=3 then do;
call define(_row_,"style","style=[font_weight=bold background=light_grey]");
end;
endcomp;
break after service / page;
rbreak after / ol;

run;
SAS Super FREQ
Posts: 8,864

Re: Proc Report

Hmmm, well....

If you must use DISPLAY, then you may have to "fake" out PROC REPORT by making a "dummy" variable that will allow you to write a line. I've presummarized some data in the program below and then show you how some of the COMPUTE after LINE statements look, depending on which variable I am breaking before or after.

Note the use of NOPRINT in the later example sections and how it "hides" the fake variables but still allows me to use them for break processing.

cynthia
[pre]
** Presummarize SASHELP.CLASS height;
proc means data=sashelp.class n min mean median max;
var height;
class age;
output out=work.presum n(height)=acnt
min(height)=amin
mean(height)=amean
median(height)=amed
max(height)= amax;
run;

** sort summarized data by age;
proc sort data=work.presum;
by age;
run;

** look at presummarized data;
ods listing;
proc print data=work.presum;
run;

** make some "fake" variables for ordering and breaking;
data work.final;
set presum;
by age;
ordvar = _n_;
retain flag;

if age le 13 then specbrk = 0;
else if age gt 13 then specbrk = 1;

if _type_ = 0 then do; aflag = 999; ordvar = 999; specbrk = 999; end;
allobs = 'x';
run;

** Default report with "fake" variables;
ods listing close;
ods html file='c:\temp\fake_vars1.html' style=sasweb;
proc report data=work.final nowd;
title '1) All variables revealed including "fake" variables';
column allobs specbrk ordvar age amin amean amed amax;
define allobs / order;
define specbrk / order;
define ordvar / order;
define age / display f=3.;
define amin / display f=6.2;
define amean / display f=6.2;
define amed / display f=6.2;
define amax / display f=6.2;
run;
ods html close;

** Example 2;
ods html file='c:\temp\fake_vars2.html' style=sasweb;
proc report data=work.final nowd;
title '2) COMPUTE BEFORE/AFTER ALLOBS';
column allobs specbrk ordvar age amin amean amed amax;
define allobs / order;
define specbrk / order;
define ordvar / order;
define age / display f=3.;
define amin / display f=6.2;
define amean / display f=6.2;
define amed / display f=6.2;
define amax / display f=6.2;
compute before _page_;
line 'This is from COMPUTE BEFORE _PAGE_';
endcomp;
compute after _page_;
line 'This is from COMPUTE AFTER _PAGE_';
endcomp;
compute before;
line 'This is from COMPUTE BEFORE';
endcomp;
compute after;
line 'This is from COMPUTE AFTER';
endcomp;
compute before ALLOBS;
line 'This is from COMPUTE BEFORE ALLOBS';
endcomp;
compute after ALLOBS;
line 'This is from COMPUTE AFTER ALLOBS';
endcomp;
run;

ods html close;

** Example 3;
ods html file='c:\temp\fake_vars3.html' style=sasweb;
proc report data=work.final nowd;
title '3) compute BEFORE/AFTER ORDVAR with NOPRINT';
column allobs ordvar specbrk age amin amean amed amax;
define allobs / order noprint;
define ordvar / order noprint;
define specbrk / order noprint;
define age / display f=3.;
define amin / display f=6.2;
define amean / display f=6.2;
define amed / display f=6.2;
define amax / display f=6.2;
compute before ORDVAR;
line 'This is from COMPUTE BEFORE ORDVAR';
endcomp;
compute after ORDVAR;
line 'This is from COMPUTE AFTER ORDVAR';
endcomp;
run;
ods html close;

** Example 4;
ods html file='c:\temp\fake_vars4.html' style=sasweb;
proc report data=work.final nowd;
title '4) compute BEFORE/AFTER SPECBRK with NOPRINT';
column allobs specbrk age amin amean amed amax;
define allobs / order noprint;
define specbrk / order noprint;
define age / display f=3.;
define amin / display f=6.2;
define amean / display f=6.2;
define amed / display f=6.2;
define amax / display f=6.2;
compute before SPECBRK;
line 'This is from COMPUTE BEFORE SPECBRK';
endcomp;
compute after SPECBRK;
line 'This is from COMPUTE AFTER SPECBRK';
endcomp;
run;
ods html close;


[/pre]
Occasional Contributor
Posts: 19

Re: Proc Report

Wow, thanks. I was thinking that I would just have to create a fake var to do this but your examples really show how they work out. One question, on the LINE statement I want to use the PRODUCT with its description in a format. Could I use: LINE put(PRODUCT,[format]) ?
SAS Super FREQ
Posts: 8,864

Re: Proc Report

Hi:
Yes, but (generally)I prefer to assemble my whole line like this using a temporary variable -- especially if I want/need to use a PUT to expand a value:
[pre]
compute ...;
length myline $150;
myline = 'The product is: '||put(product,$pfmt.);
lgvar = length(myline);
line myline $varying. lgvar;
endcomp;
[/pre]

And, there are a few other caveats --

1) PROC REPORT works from LEFT to RIGHT...so, for example if you have this COLUMN statement:
[pre]
column var1 var2 var3 var4;
[/pre]

PROC REPORT has no visibility of VAR4 when it is putting VAR1 on the report. This can affect a COMPUTE block ,such as when you are calculating a new report item. For example, if you wanted to compute the total of VAR3 and VAR4 into an item called TOT, then your COLUMN statement and COMPUTE block would have to be:
[pre]
column var1 var2 var3 var4 TOT;
... more code...
compute TOT;
TOT = sum(var3,var4);
endcomp;
[/pre]

but, you could NOT have:
[pre]
column var1 var2 TOT var3 var4 ;
... more code...
compute TOT;
TOT = sum(var3,var4);
endcomp;
[/pre]

That above assignment statement would be the one needed if VAR3 and VAR4 were both DISPLAY usage. IF they had been SUM usage, then the correct assignment statement needed would change to the "compound" name variable.statistic:
TOT=sum(var3.sum, var4.sum);


2) when you are dealing with GROUP or ORDER items, and you want to use a value at the break (such as you want to to with PRODUCT, you have to realize that by default, PROC REPORT suppresses the repetitive values of GROUP or ORDER items. So, using the above data, if you wanted to show values for ALLOBS and SPECBRK at the "AFTER" for the break processing, you would have to create temporary variables to hold the values -- because at the AFTER location, the value for a GROUP or ORDER variable is blank. If you wanted to show values for ALLOBS and SPECBRK at the "BEFORE" location for break processing, you'd be able to use use the value of the variable because on the first report row for a break variable, the value is available.

So, you may have to "grab" the value of product and "save it" for later use in your line statement. This paper is a good resource on PROC REPORT and how to avoid some of the common "beginner" mistakes that folks frequently make with PROC REPORT:
http://support.sas.com/resources/papers/proceedings10/133-2010.pdf

cynthia
[pre]
ods listing close;
ods html file='c:\temp\left_right.html' style=sasweb;
proc report data=work.final nowd;
title '1) Using a group or order value at a break';
column allobs specbrk ordvar age amin amean amed amax;
define allobs / order;
define specbrk / order;
define ordvar / order;
define age / display f=3.;
define amin / display f=6.2;
define amean / display f=6.2;
define amed / display f=6.2;
define amax / display f=6.2;
compute before allobs;
length hold_ao $3;
hold_ao = allobs;
endcomp;
compute before specbrk/ style={font_weight=bold just=l};
length hold_sp 3.;
hold_sp = specbrk;
line 'The current value of specbrk at BEFORE is ' specbrk 3.;
endcomp;
compute after specbrk / style={font_weight=bold just=l};
line 'The current value of specbrk at AFTER is ' specbrk 3.;
line 'The current value of specbrk(held) is ' hold_sp 3.;
endcomp;
compute after/ style={font_weight=bold just=l};
line 'The value of allobs at AFTER is: ' allobs $3.;
line 'The value of allobs(held) is: ' hold_ao $3.;
endcomp;
run;
ods html close;

[/pre]
Occasional Contributor
Posts: 19

Re: Proc Report

I got it working! Thanks very much Cynthia!
Ben
Ask a Question
Discussion stats
  • 6 replies
  • 224 views
  • 0 likes
  • 2 in conversation