topic Re: How do I reference the values from vertical macro list in SAS Programming

How do I reference the values from vertical macro list

Easybeat — Fri, 04 Jun 2021 13:03:11 GMT

Hi! I'm working on SAS EG.
I have a task of automatically running the program several times, producing several data sets.
Every time the program should run but change the date on which the data is loaded.
The program should take each date from vertical macro list (data set "Dates" in code below).
So, for example, user set the range of dates and the program should run on every date in that range.

The code first make the list of dates. Then it creates the vertical macro list with macro variable obs_date and macro variable numdates which stores number of days. Then it executes main code of the program.
The code that I have:

%let BeginDate = 01jan2019;
%let EndDate = 01mar2019;
%let Months = intck('month',"&BeginDate."d,"&EndDate."d);

data dates;
    do m = 0 to &months.;
    first_of_month = intnx('month',"&BeginDate."d,m,'s');
     last_of_month = intnx('month', "&BeginDate."d,m,'e');
output;
end;
format first_of_month last_of_month date9.;
run;

%Macro pos;
data _null_;
set dates end=eof;
call symputx(cats('obs_date',_N_),last_of_month);
if eof then call symputx('numdates',_n_);
run;

%do i=1 %to &numdates.;

%let date_= %sysfunc(putn(%sysfunc(inputn(&&obs_date&i,anydtdte9.)),datetime20.));

proc sql;

CREATE TABLE WORK.OPEN_POS_&&obs_date&i (COMPRESS=YES) as
SELECT	f1.Name as PARTY_NAME,
	...code...
from EQ_TRADES t
		...joins...
where (t.TRADE_DATE<=&date_  and t.DUE_DATE>=&date_ and t.TRADE_DATE<t.DUE_DATE) and t.FIRM_ID in (SELECT * FROM work.inn)
	
quit;

%end;
%mend pos;
%pos

As a result I have got nothing. Several empty data sets. Which is wrong.

As I understand something is wrong with referencing the macro list values.
So I think something is wrong here:

%let date_= %sysfunc(putn(%sysfunc(inputn(&&obs_date&i,anydtdte9.)),datetime20.));

or here:

where (t.TRADE_DATE<=&date_  and t.DUE_DATE>=&date_ and t.TRADE_DATE<t.DUE_DATE)

If I write this code:

%let date_= "%sysfunc(putn(%sysfunc(inputn(&&obs_date&i,anydtdte9.)),date9.)):0:0:0"dt;

Then SAS says "Invalid date/time/datetime constant "________ .:0:0:0"dt".

The values stored in t.TRADE_DATE and in t.DUE_DATE are in datetime20. format.
The values in vertical macro list are in SAS format (numeric values from 01.01.1960).

Please help to solve this problem. Thank you!

Re: How do I reference the values from vertical macro list

Tom — Fri, 04 Jun 2021 13:12:31 GMT

This first problem I see is the third line.

%let Months = intck('month',"&BeginDate."d,"&EndDate."d);

If you want MONTHS to be a number you need to wrap the INTCK() function in in the %SYSFUNC() macro function.

Also remove the quotes.

%let Months = %sysfunc(intck(month,"&BeginDate."d,"&EndDate."d));

Re: How do I reference the values from vertical macro list

PaigeMiller — Fri, 04 Jun 2021 13:14:06 GMT

As I understand something is wrong with referencing the macro list values.

Whenever you have problems like this with a macro, please run the command

options mprint symbolgen mlogic;

and then re-run your macro. This adds additional information into the LOG that can help you debug the macro.

If you still can't figure out what is wrong, then please show us the LOG for this macro (we need to see 100% of the log of a run of the macro, all of it, with nothing chopped out). When you show us the LOG, please copy the log as text and paste it into the window that appears when you click on the </> icon. This formats the log to make it much more readable, and helps us help you. DO NOT SKIP THIS STEP.

Re: How do I reference the values from vertical macro list

Tom — Fri, 04 Jun 2021 13:42:28 GMT

It looks like you are generating a dataset with DATE values. And your SQL code needs DATETIME values.

At some point you need to convert from one to the other.

Since your data step is putting the raw number of days in to the macro "array" you could convert it at the point of creating the macro variable DATE_ .

%let date_= %sysfunc(dhms(&&obs_date&i,0,0,0));

Now DATE_ will have the raw number of seconds datetime value instead of the raw number of days date value that was in OBS_DATE1. So you can use it to compare to your datetime variables in your SQL code.

t.TRADE_DATE<=&date_

Or just generate DATETIME values into the macro variables.

So here is a cleaned up version of your macro:

%macro pos(dsn=dates);
%local i ymd;

data _null_;
  if eof then call symputx('numdates',_n_-1,'L');
  set &dsn end=eof;
  call symputx(cats('obs_date',_N_),dhms(last_of_month,0,0,0),'L');
run;

proc sql;
%do i=1 %to &numdates.;
  %let ymd=%sysfunc(datepart(&&obs_date&i),yymmddn8.);
  CREATE TABLE WORK.OPEN_POS_&ymd (COMPRESS=YES) as
  SELECT f1.Name as PARTY_NAME
   ...code...
  from EQ_TRADES t
    ...joins...
  where (t.TRADE_DATE<=&&obs_date&i and t.DUE_DATE>=&&obs_date&i and t.TRADE_DATE<t.DUE_DATE)
    and t.FIRM_ID in (SELECT * FROM work.inn)
  ;
%end;
quit;
%mend pos;
%pos;

Re: How do I reference the values from vertical macro list

Easybeat — Fri, 04 Jun 2021 13:51:30 GMT

Yep. This %let date_= %sysfunc(dhms(&&obs_date&i,0,0,0)); works perfectly!
Thank you a lot!

The code works slower than I thought, though 😄 Will try later your cleaned up version, maybe it will make it faster:)