topic Re: macro variable(create case statement) with do iteration in proc sql select in New SAS User

macro variable(create case statement) with do iteration in proc sql select

andreibuzurniuc — Fri, 26 Jun 2020 09:20:27 GMT

hello! i have a question, this macro code 'casecomis(nr)' can work in proc sql select ? in this moment i how error .

%let edd = %sysfunc(putn("&data_fin"d, ddmmyy6.)); %put &edd; /*from prompt*/

%let beg = %sysfunc(putn("&data_ini"d, ddmmyy6.)); %put &beg; /*from prompt*/

%let nr = &nrluni; /*from prompt*/

%put &nr;

%let S1 = %sysfunc(intnx(month,"&data_fin"d,-0,end),yymmn4.); %put &S1;

%let S2 = %sysfunc(intnx(month,"&data_fin"d,-1,end),yymmn4.); %put &S2;

%let S3 = %sysfunc(intnx(month,"&data_fin"d,-2,end),yymmn4.); %put &S3;

%macro casecomis(nr);

%do j=0 %to &nr;

%let i=%eval(&j+1);

%let luna= &S&i;

%let separatorb =%str(,);

%let varbeg= %sysfunc(intnx(month,"&data_fin"d,-&j,BEGINNING));

%let varend= %sysfunc(intnx(month,"&data_fin"d,-&j,end));

%let varcase= %sysfunc(case when (data_pos >=&varbeg and data_pos<=&varend) then SF else 0 end as INC_&luna);

%put &varcase;

%put &separatorb;

%end;

%let aa= INC_&S1;

%do i=2 %to &nr;

%let total=%eval(&aa+((INC_&&S&j));

%put &total;

%end;

%mend casecomis;

proc sql;

Create table ComSS as select * , %casecomis(&nr) as INC_Total

from ExemplComss;

Log error:

301 proc sql;

302 Create table ComisioaneSF as select * , %casecomis(&nr) as INC_Total
WARNING: Apparent symbolic reference S not resolved.
ERROR: Expected open parenthesis after macro function name not found.
4 The SAS System 10:17 Friday, June 26, 2020

(data_pos >=22036 and data_pos<=22066) then SF else 0 end as INC_2005)
,
WARNING: Apparent symbolic reference S not resolved.
ERROR: Expected open parenthesis after macro function name not found.
(data_pos >=22006 and data_pos<=22035) then SF else 0 end as INC_2004)
,
302 Create table ComSS as select * , %casecomis(&nr) as INC_Total
_________
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, AS,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

303 from ExemplComss;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

Thanks!!!

Re: macro variable(create case statement) with do iteration in proc sql select

Kurt_Bremser — Fri, 26 Jun 2020 09:36:56 GMT

%SYSFUNC can only be used to execute data step functions in a macro statement. You cannot execute other code with it. So

%sysfunc(case when (

can't work.

Since &S1 contains a period, this

 %let aa= INC_&S1;
   %do i=2 %to &nr;
      %let total=%eval(&aa+((INC_&&S&j));

will resolve to

%let total=%eval(INC_202001+((INC_202001));

Since %eval is designed to calculate with integers, it cannot handle the text(!) it is given as argument.

Just the first two things that came to my attention.

The fact that you have data (periods) in structure (column names) lets me suspect a design problem, so you should contemplate changing your dataset layout to long. (see Maxim 19)

For in-depth help, supply example data in usable form (self-contained data step, usually with datalines), and what you want to get out of it.

Re: macro variable(create case statement) with do iteration in proc sql select

PaigeMiller — Fri, 26 Jun 2020 10:43:50 GMT

@andreibuzurniuc

Some advice — the first step, BEFORE writing any macro, is to create legal valid working SAS code.for a small portion of your situation, without macros and without macro variables. You clearly haven't done this. So let me say that if you don't have legal valid working SAS code without macros and without macro variables, then it will not work when you try to convert it to a macro.