topic Re: PROC Report in SAS Programming

PROC Report

Anuz — Thu, 20 May 2021 18:10:47 GMT

Hi,

I wish to create a COMPUTE statement with a variable value.

For eg :

The value for jump can range from 1 to 50 depending on the dataset being handled.

I am hoping maybe I can create a macro as below. It throws an error however as it doesnt like the if statment etc

ERROR 180-322: Statement is not valid or it is used out of proper order.

What I want to be able to do is call this macro within a proc report based on what value I need to assign for jump.

If its not a macro is there another way to do it please. All I really want is to have the compute statement created by replacing the value for jump within the compute statment.

%macro Name(jump);

COMPUTE NAME&jump.;

if CR&jump.=1 AND CL&jump.=1 then call define ("COL&jump.",'style','style={font_weight=bold background=&clr1.}');
if CR&jump.=2 AND CL&jump.=1 then call define ("COL&jump.",'style','style={font_weight=bold background=&clr2.}');
if CR&jump.=3 AND CLR&jump.=1 then call define ("COL&jump.",'style','style={font_weight=bold background=&clr3.}');
if CR&jump.=4 AND CLR&jump.=1 then call define ("COL&jump.",'style','style={font_weight=bold background=&clr4.}');

endcomp;

%mend;

Re: PROC Report

PaigeMiller — Thu, 20 May 2021 18:20:44 GMT

Please run the following command

options mprint;

at the start of your program, run the program again and show us the LOG. We need to see the ENTIRE log for PROC REPORT, that's 100%, every single character of the LOG for PROC REPORT — do not pick and choose parts of the log for PROC REPORT to show us. Please preserve the formatting in the log by copying the log as text and then pasting it into the window that appears when you click on the </> icon — DO NOT SKIP THIS STEP.

Re: PROC Report

Kurt_Bremser — Thu, 20 May 2021 18:22:46 GMT

Run the code without the macro definition, and set jump with a %LET.

Then, post the log by copy/pasting it into a window opened with this button:

You also seem to use macro variables clr1 to clr4 within single quotes.

Re: PROC Report

Cynthia_sas — Thu, 20 May 2021 18:29:36 GMT

Hi:
I always recommend getting things working without macro because it is easier to debug. You don't show ALL your code and you don't show your ERROR messages in the log. One reason it may not like your IF statement is that you may be referencing the variable incorrectly. Remember that for ANALYSIS items, you need to use a compound name: variable.statistic, as shown below:

But that is just a guess...you don't get any error messages with incorrect usage, just a note in the log for the variable that was referenced incorrectly.

Otherwise, without seeing ALL your code and your ODS statements and your LOG messages, it is hard to comment. Also, without providing any data, then people cannot run your code without making some fake data, which may or may not be structured correctly.

Cynthia

Re: PROC Report

Anuz — Thu, 20 May 2021 19:14:29 GMT

Apologies if my query wasnt that clear. I have now included my proc report sample that works. hope this helps.

ods excel file="&xl_path./TEST.xlsx" options(
autofilter="yes" zoom='80' embedded_titles='yes' embedded_footnotes='yes'
sheet_name="TEST"
);
options validvarname=UPCASE label=0;

proc report data=TEST_RPT style(column)={tagattr='wraptext:no' }
style(header)={just=l fontfamily=arial fontweight=bold foreground=black background=tan fontsize=2.5}
style(column)={fontfamily=coolvetica fontsize=2};
column CLR1-CLR&mcolcnt. CLFR1-CLFR&mcolcnt. &mcols. ;
define CLFR1-CLFR&mcolcnt. /display noprint;
define CLR1-CLR&mcolcnt. /display noprint;

%j_sel;
run;

/*The macro j_sel */

%macro j_sel;

%if &mcolcnt. eq 3 %then %do;

%name1; %name2;
%end;

%else %if &mcolcnt. eq 4 %then %do;
%name1; %name2; %name3;
%end;
%mend;

Based on the value of variable mcolcnt, I want to have as many COMPUTE Statements created.

At the moment I have macro name1, name2 and name3 written as below and this works perfectly fine. My proc report creates the report that I want

But the issue is say I have say 10 or 40 NAME columns from NAME1 through to NAME 40, then I dont want to be writing as many COMPUTE Statements.

So I was hoping if I can some sort of a %macro name(jump); If i just call that and pass the number 1 through to 4 then I dont have to write as many name macros.

%macro NAME1;
COMPUTE NAME1;

if CLR1=1 AND CLFR1=1 then
call define ('NAME1','style','style={font_weight=bold background=&clr1.}');

if CLR1=2 AND CLFR1=1 then
call define ('NAME1','style','style={font_weight=bold background=&clr2.}');

if CLR1=3 AND CLFR1=1 then
call define ('NAME1','style','style={font_weight=bold background=&clr3.}');

if CLR1=4 AND CLFR1=1 then
call define ('NAME1','style','style={font_weight=bold background=&clr4.}');
endcomp;
%mend;

%macro NAME2;
COMPUTE NAME2;

if CLR2=1 AND CLFR2=1 then
call define ('NAME2','style','style={font_weight=bold background=&clr1.}');

if CLR2=2 AND CLFR2=1 then
call define ('NAME2','style','style={font_weight=bold background=&clr2.}');

if CLR2=3 AND CLFR2=1 then
call define ('NAME2','style','style={font_weight=bold background=&clr3.}');

if CLR2=4 AND CLFR2=1 then
call define ('NAME2','style','style={font_weight=bold background=&clr4.}');
endcomp;
%mend;

Re: PROC Report

Anuz — Thu, 20 May 2021 19:33:31 GMT

I have now added some more info on my ods and proc report below.

Re: PROC Report

ballardw — Thu, 20 May 2021 19:49:49 GMT

Where is an example of the code that worked properly without any macros?

Re: PROC Report

PaigeMiller — Thu, 20 May 2021 21:00:49 GMT

@Anuz we specifically asked to see the LOG and we specifically gave instructions how to provide the LOG to us.

Re: PROC Report

Anuz — Fri, 21 May 2021 09:03:18 GMT

Hi,

Below is what I would run without the macros. In this particular instance I have only two name columns - NAME1 and NAME2. So its fine. But I wouldn't know how many NAME columns could be there before hand. So want a way where the COMPUTE statements can be populated based on the number of NAME columns that appear in the final dataset that I need to run a report for.

ods excel file="&xl_path./TEST.xlsx" options(
autofilter="yes" zoom='80' embedded_titles='yes' embedded_footnotes='yes'
sheet_name="TEST"
);
options validvarname=UPCASE label=0;

proc report data=TEST_RPT style(column)={tagattr='wraptext:no' }
style(header)={just=l fontfamily=arial fontweight=bold foreground=black background=tan fontsize=2.5}
style(column)={fontfamily=coolvetica fontsize=2};
column CLR1-CLR&mcolcnt. CLFR1-CLFR&mcolcnt. &mcols.;
define CLFR1-CLFR&mcolcnt. /display noprint;
define CLR1-CLR&mcolcnt. /display noprint;

COMPUTE NAME1;

if CLR1=1 AND CLFR1=1 then
call define ('NAME1','style','style={font_weight=bold background=&clr1.}');

if CLR1=2 AND CLFR1=1 then
call define ('NAME1','style','style={font_weight=bold background=&clr2.}');

if CLR1=3 AND CLFR1=1 then
call define ('NAME1','style','style={font_weight=bold background=&clr3.}');

if CLR1=4 AND CLFR1=1 then
call define ('NAME1','style','style={font_weight=bold background=&clr4.}');
endcomp;

COMPUTE NAME2;

if CLR2=1 AND CLFR2=1 then
call define ('NAME2','style','style={font_weight=bold background=&clr1.}');

if CLR2=2 AND CLFR2=1 then
call define ('NAME2','style','style={font_weight=bold background=&clr2.}');

if CLR2=3 AND CLFR2=1 then
call define ('NAME2','style','style={font_weight=bold background=&clr3.}');

if CLR2=4 AND CLFR2=1 then
call define ('NAME2','style','style={font_weight=bold background=&clr4.}');
endcomp;
run;

Re: PROC Report

Anuz — Fri, 21 May 2021 09:21:45 GMT

@PaigeMiller - Sorry I don't know where the </> icon is. I am new to this forum and SAS. So maybe I am missing something basic here. But I have pasted the full log below

I want to be able to call this macro in the below proc report so it creates the COMPUTE STATEMENT based on the number I pass to the macro.

proc report data=TEST_RPT style(column)={tagattr='wraptext:no' }
style(header)={just=l fontfamily=arial fontweight=bold foreground=black background=tan fontsize=2.5}
style(column)={fontfamily=coolvetica fontsize=2};
column CLR1-CLR&mcolcnt. CLFR1-CLFR&mcolcnt. &mcols. ;
define CLFR1-CLFR&mcolcnt. /display noprint;
define CLR1-CLR&mcolcnt. /display noprint;

%j(14);
run;

==========MACRO===================================
The macro is as below
%macro j(num);
compute NAME&num.;

if CLFR&num.=1 AND CLR&num.=1 then
call define ("NAME&num.",'style','style={font_weight=bold background=&clr1.}');

if CLFR&num.=2 AND CLR&num.=1 then
call define ("NAME&num.",'style','style={font_weight=bold background=&clr2.}');

if CLFR&num.=3 AND CLR&num.=1 then
call define ("NAME&num.",'style','style={font_weight=bold background=&clr3.}');

if CLFR&num.=4 AND CLR&num.=1 then
call define ("NAME&num.",'style','style={font_weight=bold background=&clr4.}');
endcomp;
%mend;

%j(14);

===================LOG=========================================

1 The SAS System

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program (7)';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=ACTIVEX;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HTMLBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 options mprint;
27
28 %macro j(num);
29
30 compute NAME&num.;
31 if CLFR&num.=1 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold background=&clr1.}');
32 if CLFR&num.=2 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold background=&clr2.}');
33 if CLFR&num.=3 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold background=&clr3.}');
34 if CLFR&num.=4 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold background=&clr4.}');
35
36 endcomp;
37
38 %mend;
39
40
41 %j(14);
NOTE: Line generated by the invoked macro "J".
41 compute NAME&num.; if CLFR&num.=1 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold
_______
180
41 ! background=&clr1.}'); if CLFR&num.=2 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold
41 ! background=&clr2.}'); if
ERROR 180-322: Statement is not valid or it is used out of proper order.

MPRINT(J): compute NAME14;

NOTE: Line generated by the invoked macro "J".
41 compute NAME&num.; if CLFR&num.=1 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold
__
180
41 ! background=&clr1.}'); if CLFR&num.=2 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold
41 ! background=&clr2.}'); if
2
ERROR 180-322: Statement is not valid or it is used out of proper order.

MPRINT(J): if CLFR14=1 AND CLR14=1 then call define ("NAME14",'style','style={font_weight=bold background=&clr1.}');

NOTE: Line generated by the invoked macro "J".
41 compute NAME&num.; if CLFR&num.=1 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold
41 ! background=&clr1.}'); if CLFR&num.=2 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold
__
180
41 ! background=&clr2.}'); if
ERROR 180-322: Statement is not valid or it is used out of proper order.

MPRINT(J): if CLFR14=2 AND CLR14=1 then call define ("NAME14",'style','style={font_weight=bold background=&clr2.}');

NOTE: Line generated by the invoked macro "J".
41 compute NAME&num.; if CLFR&num.=1 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold
41 ! background=&clr1.}'); if CLFR&num.=2 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold
41 ! background=&clr2.}'); if
__
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

MPRINT(J): if CLFR14=3 AND CLR14=1 then call define ("NAME14",'style','style={font_weight=bold background=&clr3.}');

NOTE: Line generated by the invoked macro "J".
41 CLFR&num.=3 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold background=&clr3.}'); if
__
180
41 ! CLFR&num.=4 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold background=&clr4.}'); endcomp;
ERROR 180-322: Statement is not valid or it is used out of proper order.

MPRINT(J): if CLFR14=4 AND CLR14=1 then call define ("NAME14",'style','style={font_weight=bold background=&clr4.}');

NOTE: Line generated by the invoked macro "J".
41 CLFR&num.=3 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold background=&clr3.}'); if
41 ! CLFR&num.=4 AND CLR&num.=1 then call define ("NAME&num.",'style','style={font_weight=bold background=&clr4.}'); endcomp;
_______
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

MPRINT(J): endcomp;
42
43 GOPTIONS NOACCESSIBLE;
44 %LET _CLIENTTASKLABEL=;
45 %LET _CLIENTPROCESSFLOWNAME=;
46 %LET _CLIENTPROJECTPATH=;
47 %LET _CLIENTPROJECTPATHHOST=;
48 %LET _CLIENTPROJECTNAME=;
49 %LET _SASPROGRAMFILE=;
50 %LET _SASPROGRAMFILEHOST=;
51
52 ;*';*";*/;quit;run;
53 ODS _ALL_ CLOSE;
54
55
56 QUIT; RUN;
57

Re: PROC Report

Kurt_Bremser — Fri, 21 May 2021 10:10:16 GMT

Please read this post again and use the indicated button for posting the log!

The post includes a picture that shows the button.

And use the "little running man" right next to it for code.

Run this code:

proc report data=TEST_RPT style(column)={tagattr='wraptext:no' }
style(header)={just=l fontfamily=arial fontweight=bold foreground=black background=tan fontsize=2.5}
style(column)={fontfamily=coolvetica fontsize=2};
column CLR1-CLR&mcolcnt. CLFR1-CLFR&mcolcnt. &mcols. ;
define CLFR1-CLFR&mcolcnt. /display noprint;
define CLR1-CLR&mcolcnt. /display noprint;
%let num=14;
compute NAME&num.;

if CLFR&num.=1 AND CLR&num.=1 then
call define ("NAME&num.",'style','style={font_weight=bold background=&clr1.}');

if CLFR&num.=2 AND CLR&num.=1 then
call define ("NAME&num.",'style','style={font_weight=bold background=&clr2.}');

if CLFR&num.=3 AND CLR&num.=1 then
call define ("NAME&num.",'style','style={font_weight=bold background=&clr3.}');

if CLFR&num.=4 AND CLR&num.=1 then
call define ("NAME&num.",'style','style={font_weight=bold background=&clr4.}');
endcomp;

run;

and post the log as requested.

PS the buttons may not be there if you hit "quick reply" or answer directly from a mail. Open communities.sas.com in a browser (Chrome always works for me) to post here.

Re: PROC Report

Anuz — Fri, 21 May 2021 14:11:42 GMT

@Kurt_Bremser - Thank you very much. The code you gave worked just fine. Also thank you pointing out that the Quick Reply wouldn't give me the right options to post my log. I was using the Quick reply.

So what you gave works and gives me the desired output. My issue here is I wouldn't know how many COMPUTE statements I would need to include. Depending on the number of NAME columns I will need to write as many COMPUTE statements. So if my final dataset has 5 NAME columns, I need COMPUTE statements for NAME1 through to NAME5. How can I get the code to create them automatically. Can I write a DO or FOR LOOP within the proc report to enable that or some sort of macro. Thank you.

Re: PROC Report

Kurt_Bremser — Fri, 21 May 2021 15:34:23 GMT

Let's progress one step further:

proc report data=TEST_RPT style(column)={tagattr='wraptext:no' }
style(header)={just=l fontfamily=arial fontweight=bold foreground=black background=tan fontsize=2.5}
style(column)={fontfamily=coolvetica fontsize=2};
column CLR1-CLR&mcolcnt. CLFR1-CLFR&mcolcnt. &mcols. ;
define CLFR1-CLFR&mcolcnt. /display noprint;
define CLR1-CLR&mcolcnt. /display noprint;
%macro j(num);
compute NAME&num.;
if CLFR&num.=1 AND CLR&num.=1 then
call define ("NAME&num.",'style','style={font_weight=bold background=&clr1.}');
if CLFR&num.=2 AND CLR&num.=1 then
call define ("NAME&num.",'style','style={font_weight=bold background=&clr2.}');
if CLFR&num.=3 AND CLR&num.=1 then
call define ("NAME&num.",'style','style={font_weight=bold background=&clr3.}');
if CLFR&num.=4 AND CLR&num.=1 then
call define ("NAME&num.",'style','style={font_weight=bold background=&clr4.}');
endcomp;
%mend;
%j(14)
run;

Copy/paste the code to your environment and run it.

Re: PROC Report

Anuz — Fri, 21 May 2021 17:01:38 GMT

that worked as well 🙂 I was then able to include a do loop to have the multiple COMPUTE statements done.

Thanks a ton for your help @Kurt_Bremser... Appreciate it.