topic Dates in a loop as a condition for a data pull in SAS Programming

Dates in a loop as a condition for a data pull

jlee8 — Fri, 11 Dec 2020 18:05:08 GMT

Hi,

I'm trying to create two datasets based on specified dates. The "pull_data" macro does the actually retrieval of data and I just wanted to incorporate it into a new macro where I specify the dataset name and the date constraint for each dataset.

%MACRO test();

%let j=1;
%let vals = PRIOR CURRENT;
%let dates = "24oct2017" "11aug2020";
%do %while(%scan(&vals,&j) ne );
%let i=%scan(&vals, &j);
%let k=input(%scan(&dates, &j),date9.);

%pull_data()

%let j=%eval(&j+1);
%end;
%let j=1;
%MEND;

%test();

However, the code errors out and I get the following message.

SYMBOLGEN: Macro variable K resolves to input("24oct2017",date9.)
ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token ")" was found following
"t("24oct2017",date9.". Expected tokens may include: "<identifier>". SQLSTATE=42601

The code runs fine if I comment out the lines associated with dates.

Please advice on how to fix this problem.

Thank you!

Re: Dates in a loop as a condition for a data pull

Tom — Fri, 11 Dec 2020 18:19:27 GMT

First problem is this line

%let k=input(%scan(&dates, &j),date9.);

The macro processor will evaluate the items with triggers (% and &) so when J=1 it is the same as:

%let k=input("24oct2017",date9.);

Now you could use &k to generate a SAS statement like:

  date =  &k;

Which would work in a data step since the data step compiler will see:

date = input("24oct2017",date9.);

So whether that is an issue or not depends on how the macro variable K is used in the macro you called but didn't provide.

Since your strings are already close to the valid syntax for date literals you might try just using those.

%let k=%scan(&dates, &j)d ;

If you really wanted to store the date value (number of days since 1960) in the macro variable K then you can use %SYSFUNC() to run the input in the macro processor. But then you need to use the INPUTN() function. And you need to lose the quotes.

%let dates = 24oct2017 11aug2020;
...
%let k=%sysfunc(inputN(%scan(&dates, &j),date9.));

Re: Dates in a loop as a condition for a data pull

jlee8 — Fri, 11 Dec 2020 19:31:05 GMT

Thanks Tom.

I do use &k as you mentioned in my proc sql statement like this: WHERE DT = &k; but still get the same error.

I tried the other suggestion using the sysfunc but ended up with another error:

SYMBOLGEN: Macro variable K resolves to 21116
ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0401N The data types of the operands for the operation "=" are not
compatible or comparable. SQLSTATE=42818

So still looking for answers.

Re: Dates in a loop as a condition for a data pull

Tom — Fri, 11 Dec 2020 19:36:44 GMT

You left out the fact that you are trying to generate DB2 code and not SAS code.

So you need to know what is the DB2 equivalent of the SAS code:

WHERE DT = "01JAN2020"d

This requires that you know what type of variable DT is in your database (date, datetime, string, number, etc) and how DB2 expects you to type a constant value for that data type.

So you might want to generate something like:

WHERE DT = DATE '2020-01-01'

Re: Dates in a loop as a condition for a data pull

ballardw — Fri, 11 Dec 2020 19:39:01 GMT

Strongly suggest that make the things that your "pull_data" macro needs as explicit parameters. Assuming that macro values exist and have the correct values the way you are coding is just waiting to be a serious problem when some logic, like making incorrect/incompatible macro variables, goes awry.

So likely your macro should be defined with something like:

%mend pull_data (inputdataset = , outputdataset=, othervar= ).

Comparison with blank for macro variables/ values is a pretty touchy problem. Instead of %do %while you might consider using an iterated %do loop using the number of values.

%macro dummy(varlist = );

   %do i=1 %to %sysfunc(countw(&varlist.));
      %let parm = %scan(&varlist.,&i.);
      %put The parm is: &parm.;
      /* code that uses the parm goes here*/
   %end;
%mend;

%dummy(varlist = this that another);

I suspect you are abusing the input function in the macro variable K but since you have not bothered to show the code that actually uses the variable this is supposition.

Best practice when debugging macros: set OPTIONS MPRINT; before running the macro.

When you get the errors you need to show the log for the entire result of calling the macro. Copy from the log and paste into a code box opened on the forum with the </> icon to preserve formatting of the text.

Re: Dates in a loop as a condition for a data pull

jlee8 — Fri, 11 Dec 2020 20:29:21 GMT

Thanks, I have changed my code as you suggested. I will try to incorporate the other things soon.

%do i=1 %to %sysfunc(countw(&vals.));

%let x=%scan(&vals, &i);

Re: Dates in a loop as a condition for a data pull

ballardw — Fri, 11 Dec 2020 20:58:40 GMT

@jlee8 wrote:

Thanks, I have changed my code as you suggested. I will try to incorporate the other things soon.

%do i=1 %to %sysfunc(countw(&vals.));

%let x=%scan(&vals, &i);

If you have values that might be interpreted as delimiters for the SCAN or COUNTW you may need to restrict the delimiter used. The following is an example that forces both Scan and Countw to use only the blank character for a delimiter.

%macro dummy(varlist = );

   %do i=1 %to %sysfunc(countw(&varlist.,' '));
      %let parm = %scan(&varlist.,&i.,%str( ));
      %put The parm is: &parm.;
      /* code that uses the parm goes here*/
   %end;
%mend;

%dummy(varlist = this-that another);

Since Countw is a data step function you can provide a space the same as in a data step.

However the %scan as macro function behaves a bit differently and uses the %str( ) function to indicate what is between the ( ) should be treated as a value.

Re: Dates in a loop as a condition for a data pull

Tom — Fri, 11 Dec 2020 21:43:50 GMT

It is a little dangerous to use both single quote and space as the delimiter in the call to COUNTW() but then use only space in the call to %SCAN().