topic Re: Include macro condition in report in SAS Programming

Include macro condition in report

suresh123 — Tue, 04 Dec 2018 12:43:43 GMT

Below is the small piece of code to get reports in excel.

%if &linear %then %do;
ods excel options(sheet_name="vol");
proc print data=perf;
id direction segment;
var accts;
run;
%end;
%else %do;
ods excel options(sheet_name="vol");
proc print data=perf; 
id direction segment;
var accts;
run;
%end;


Direction segment     accts
 A          model     17177
 A          booked     567
 A          unbooked   5676
 B          model     17177
 B          booked     567
 B          unbooked   5676

If segments are not available i will get report as below

   Direction   segment    accts
     A          model     17177
     A          1         17177
     B          model     17177
     B          1         17177

Iam planing to introduce two macro variables

%let dir =A;
%let Non_segment=y;

Based on value for direction it should give only those direction and if there no segment(Non_segment=y;), it should have only first observation. So the output will looks like below for Non_segment=y

Direction  segment    accts
 A          model     17177

Re: Include macro condition in report

RW9 — Tue, 04 Dec 2018 12:52:31 GMT

And the question is?

Remembering that macro is nothing more than a find/replace system, what would the code look like without all the macro? Once that is running then macrotise it.

Re: Include macro condition in report

suresh123 — Tue, 04 Dec 2018 13:25:16 GMT

I trying to include these two macros in the codes without disturbing it much to get the required output

Re: Include macro condition in report

Astounding — Tue, 04 Dec 2018 13:57:18 GMT

Let's back up for a moment.

You have two sections of code, one when &LINEAR is true and one when it is false. Yet the code for both of them is identical. Why are there two sections of code instead of one?

Is NON_SEGMENT a variable within PERF, or is it a macro variable?

Re: Include macro condition in report

Tom — Tue, 04 Dec 2018 14:01:51 GMT

I don't understand what you are asking.

The first part makes no sense because the macro code is not doing anything. It is generating the exact same code in the THEN and ELSE branches.

The second part also doesn't make a lot of sense. Perhaps you have left out a step?

Do you just want to exclude the rows where SEGMENT='1'?

Do you want to do that conditionally? Or all of the time?

Re: Include macro condition in report

suresh123 — Tue, 04 Dec 2018 14:10:51 GMT

actually it’s small bit of code. I didn’t include every variables and code. If it is linear it gives different report and for non linear different report but both looks almost same. Iam just trying include linear with non segment and non linear with non segment in the code using macro definition( will change macro definition based on non segment or segment before running the code)

Re: Include macro condition in report

suresh123 — Tue, 04 Dec 2018 14:11:49 GMT

Non segment is macro variable which works only when I define it as Y

Re: Include macro condition in report

Astounding — Tue, 04 Dec 2018 14:16:23 GMT

Using macro language, this sort of thing is not difficult once you have seen it. For example, this code could be inserted within PROC PRINT:

%if %length(&dir) %then %do;

where direction = "&dir";

%end;

Depending on whether &DIR has a value or not, macro language is able to insert a WHERE statement within PROC PRINT.

The basic idea is that you have to know what the final SAS code should look like, and then get macro language to generate the appropriate SAS code.

Re: Include macro condition in report

Tom — Tue, 04 Dec 2018 14:20:30 GMT

What are you trying to use the macro variable NON_SEGMENT to do? How you use it determines how you should set its value. (or if instead you know what values it will have that will determine how you use it).

For example if you wanted to allow both Y and y as the value of NON_SEGMENT then your macro logic might look like this:

%if %upcase(&non_segment)=Y %then %do;
....
%end;

Re: Include macro condition in report

suresh123 — Tue, 04 Dec 2018 14:25:59 GMT

Yes if it is Y then output only first observation

Re: Include macro condition in report

suresh123 — Tue, 04 Dec 2018 14:27:15 GMT

So I want include non segment and direction in both linear and non linear

Re: Include macro condition in report

Astounding — Tue, 04 Dec 2018 14:50:54 GMT

In every case, you need to know what the SAS code would look like (without any macro language involved). The role of macro language will be to generate the proper SAS code. For example, you might decide that this would be the way to print the first observation only:

proc print data=perf (obs=1);
id direction segment;
var accts;
run;

In that case, you could modify your code using macro language to look like this:

proc print data=perf 
   %if &Non_segment = Y %then (obs=1);
   ;
id direction segment;
var accts;
run;

So if you want to include nonsegment and direction, what would the SAS code look like without macro language?

Re: Include macro condition in report

suresh123 — Tue, 04 Dec 2018 14:58:35 GMT

From above I can understand that I can use if condition in proc print

Re: Include macro condition in report

Astounding — Tue, 04 Dec 2018 16:18:39 GMT

You can never use an if condition in PROC PRINT.

You can use macro language (including %if %then) to construct the statements that become part of your program.

Macro language is a tool unto itself. It's not something you can learn by asking a few questions here and there. It typically takes two full days in a classroom to cover an introduction to macro language. There is good documentation available, if it is something you want to learn on your own. It is advisable that you have a good grasp of SAS language first, because the whole purpose of macro language is to build a SAS program. You have to be able to envision what the SAS program should look like, for macro language to be useful.

Re: Include macro condition in report

suresh123 — Sun, 09 Dec 2018 06:12:18 GMT

I am getting below error.

proc print data=new;

89 %if &Non_segment = Y %then (obs=1);

ERROR: Expected %DO not found.

ERROR: Skipping to next %END statement.

90 ;

91 id direction segment;

92 var accts;

93 run;

Re: Include macro condition in report

suresh123 — Sun, 09 Dec 2018 08:19:41 GMT

I tried below code.

%macro isblank(var);
%if %symexist(&var) %then 1; %*not exist*;
%else %if %sysevalf(%superq(&var)=,boolean) %then 1; %*blank*;
%else 0;
%mend isblank;

%let dir =A;
%let Non_segment=y;

proc print data=new;
%if %isblank(Non_segment) %then %do; obs=1;
id direction segment;
var acct;
%end;
%else %do;
proc print data =new;
id direction segment;
var acct;%end;
run;

Getting below error

74 %let dir =A;

75 %let Non_segment=y;

77 proc print data=new;

78 %if %isblank(Non_segment) %then %do; obs=1;

___

180

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

79 id direction segment;

80 var acct;

81 %end;

82 %else %do;

83 proc print data =new;

84 id direction segment;

85 var acct;%end;

86 run;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.99 seconds

cpu time 0.30 seconds

88 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

101

Re: Include macro condition in report

Astounding — Sun, 09 Dec 2018 19:41:57 GMT

This error is happening because you are not picturing what the SAS program needs to look like. Your macro code has the capability of generating this SAS program:

proc print data=new;

obs=1;

Now clearly that would generate an error. You can't stick "obs=1;" in the middle of a PROC PRINT. What you can do within PROC PRINT is:

proc print data=new (obs=1);

To do that, this is the macro code that would be most similar to your program:

proc print data=one %if %isblank(Non_segment) %then (obs=1);

;

The rest of it doesn't need to be part of the macro condition since it is the same whether the %ISBLANK condition is true or false:

id direction segment;

var acct;

run;

Re: Include macro condition in report

suresh123 — Mon, 10 Dec 2018 15:05:13 GMT

I tried running below code

%let dir =A;
%let Non_segment=y;
%let linear=1;
 
%if &linear %then %do;
ods excel options(sheet_name="vol");
proc print data=new %if %isblank(Non_segment) %then (obs=1);
;
id direction segment;
var acct;
run;
%end;
%else %do;
ods excel options(sheet_name="vol");
proc print data=new; 
var  direction segment acct;
run;
%end;

93 %let dir =A;

94 %let Non_segment=y;

95 %let linear=1;

97 %if &linear %then %do;

98 ods excel options(sheet_name="vol");

99 proc print data=new %if %isblank(Non_segment) %then (obs=1);

ERROR: Nesting of %IF statements in open code is not supported. %IF ignored.

ERROR: Skipping to next %END statement.

100 ;

101 id direction segment;

102 var acct;

103 run;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 6 observations read from the data set WORK.NEW.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.13 seconds

cpu time 0.11 seconds

104 %end;

105 %else %do;

106 ods excel options(sheet_name="vol");

107 proc print data=new;

108 var direction segment acct;

109 run;

110 %end;

111

112 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

125

What is the significance of having two semicolons

Re: Include macro condition in report

Tom — Mon, 10 Dec 2018 15:16:42 GMT

You need to semi-colon because there are two statements. One macro statement and one SAS statement. Each one needs a semi-colon.

You cannot use nested %IF statements in open code. And you cannot use %IF without %DO in open code.

If you really want to do that in OPEN code that put the optional part into a macro variable and use the %IF blocks to decide what is in the macro variable. Otherwise create a macro and call the macro.

%let obs= ;
%if &linear %then %do;
  %let obs= obs=1 ;
%end;

ods excel options(sheet_name="vol");
proc print data=new ( &obs );
%if &linear %then %do;
  id direction segment;
  var acct;
%end;
%else %do;
  var  direction segment acct;
%end;
run;

Re: Include macro condition in report

suresh123 — Mon, 10 Dec 2018 15:25:32 GMT

I think my logic is different, number of observation is based on Non-segment value