topic Re: How to create a macro variable that will take a range of date values? in SAS Programming

How to create a macro variable that will take a range of date values?

Shradha1 — Tue, 27 Apr 2021 07:19:55 GMT

I have datasets with their names indexed on date, say ABC_20210424, ABC_20210425, ABC_20210426 and so on.

I will be running this code daily where, on each day, I want to extract datasets for 60 days till yesterday.

So, if today is APR 27,2021, my start date will be 26FEB2021 and end date will be 26APR2021.

I have initialised two macro variables for these dates:

data _null_;
call symputx('SD', put(intnx('day', today(), -60, 's'), yymmddn8.));
call symputx('ED' , put(intnx('day', today(), -1, 's'), yymmddn8.));
run;

Now how do I extract all the ABC tables from 26FEB to 26APR into one table, using a macro?

Can a %DO statement help here?

Thanks in Advance!

Re: How to create a macro variable that will take a range of date values?

Oligolas — Tue, 27 Apr 2021 08:20:43 GMT

Hi,

you could use pipe to return the list of files in the path and filter the file names based on your variables for start and end:

data _null_;
call symputx('SD', put(intnx('day', today(), -60, 's'), yymmddn8.));
call symputx('ED' , put(intnx('day', today(), -1, 's'), yymmddn8.));
run;

%put &=SD.;
%put &=ED.;

%let path=C:\Temp\;
FILENAME pipedir pipe "dir ""%superq(path)\*.sas7bdat"" /b" lrecl=32767;
%*Import sas files;
DATA ds1;
  length path filename $1000 ds $32;
  infile pipedir truncover;
  input filename $char1000.;
  path="&path.";
  ds=tranwrd(filename,'.sas7bdat',''); 
  *keep files in range;
  if "&SD."<=scan(ds,2,'_')<="&ED.";
RUN;
FILENAME pipedir clear;

*Set files;
DATA ds2;
   set ds1 end=last;
   by filename;
   if _N_ eq 1 then do;
      *Define library;
      call execute('libname have "'||strip(path)||'" access=readonly;');
      call execute('DATA want; SET ');
   end;
   call execute(' have.'||strip(ds));
   if last then do;
      call execute('; RUN; ');
      call execute('libname have clear;');
   end;
RUN;

*Clean up;
PROC DELETE lib=WORK data=ds1 ds2; RUN;
%symdel SD ED;

Re: How to create a macro variable that will take a range of date values?

Kurt_Bremser — Tue, 27 Apr 2021 08:24:18 GMT

Extract the names from dictionary.tables:

%let start=%sysfunc(putn(%eval(%sysfunc(today())-60),yymmddn8.));
%let end=%sysfunc(putn(%eval(%sysfunc(today())-1),yymmddn8.));

proc sql noprint;
select catx(".",libname,memname) into :dsnames separated by " "
from dictionary.tables
where libname = "MYLIB" and memname between "ABC_&start." and "ABC_&end.";
quit;

data want;
set &dsnames.;
run;

Re: How to create a macro variable that will take a range of date values?

Shradha1 — Tue, 27 Apr 2021 09:28:46 GMT

Do I need to replace the 'dictionary.tables' part in the code? or use it as it is?
I have just replaced 'MYLIB' with my library name.
What else is to be changed in this code?

Re: How to create a macro variable that will take a range of date values?

Patrick — Wed, 28 Apr 2021 07:49:41 GMT

NOTE: THIS POST CURRENTLY MARKED AS SOLUTION IS INCORRECT AS OTHERS POINTED OUT BELOW.

Starting with your code the following should work.

data _null_;
  call symputx('SD', put(intnx('day', today(), -60, 's'), yymmddn8.));
  call symputx('ED' , put(intnx('day', today(), -1, 's'), yymmddn8.));
  stop;
run;

data want;
  set ABC_&sd - ABC_&ED;
run;

Just make sure you don't have other tables with a name like ABC_<digit> under this library.

Re: How to create a macro variable that will take a range of date values?

Kurt_Bremser — Tue, 27 Apr 2021 10:41:36 GMT

The DICTIONARY tables in SQL are metadata tables that are created on the fly when used from the current data in your libraries. The name of the table will always be the same.

Re: How to create a macro variable that will take a range of date values?

ballardw — Tue, 27 Apr 2021 16:12:47 GMT

@Patrick wrote:

Starting with your code the following should work.
data _null_;
  call symputx('SD', put(intnx('day', today(), -60, 's'), yymmddn8.));
  call symputx('ED' , put(intnx('day', today(), -1, 's'), yymmddn8.));
  stop;
run;

data want;
  set ABC_&sd - ABC_&ED;
run;
  
Just make sure you don't have other tables with a name like ABC_<digit> under this library.

Actually I think this fails with any interval that crosses a month boundary because formatted dates will not be sequential.

data abc_20210431;
   x=1;
run;

data abc_20210501;
  x=2;
run;
data abc_20210502;
  x=3;
run;
%let sd=20210431;
%let ed=20210502;

data want;
   set abc_&sd. - abc_&ed.;
run;

Generates these errors:

13   data want;
14      set abc_20210431 - abc_20210502;
ERROR: File WORK.ABC_20210432.DATA does not exist.
ERROR: File WORK.ABC_20210433.DATA does not exist.
ERROR: File WORK.ABC_20210434.DATA does not exist.
ERROR: File WORK.ABC_20210435.DATA does not exist.
ERROR: File WORK.ABC_20210436.DATA does not exist.
ERROR: File WORK.ABC_20210437.DATA does not exist.
ERROR: File WORK.ABC_20210438.DATA does not exist.
ERROR: File WORK.ABC_20210439.DATA does not exist.
ERROR: File WORK.ABC_20210440.DATA does not exist.
ERROR: File WORK.ABC_20210441.DATA does not exist.
ERROR: File WORK.ABC_20210442.DATA does not exist.
ERROR: File WORK.ABC_20210443.DATA does not exist.
ERROR: File WORK.ABC_20210444.DATA does not exist.
ERROR: File WORK.ABC_20210445.DATA does not exist.
ERROR: File WORK.ABC_20210446.DATA does not exist.
ERROR: File WORK.ABC_20210447.DATA does not exist.
ERROR: File WORK.ABC_20210448.DATA does not exist.
ERROR: File WORK.ABC_20210449.DATA does not exist.
ERROR: File WORK.ABC_20210450.DATA does not exist.
ERROR: File WORK.ABC_20210451.DATA does not exist.
ERROR: File WORK.ABC_20210452.DATA does not exist.
ERROR: File WORK.ABC_20210453.DATA does not exist.
ERROR: File WORK.ABC_20210454.DATA does not exist.
ERROR: File WORK.ABC_20210455.DATA does not exist.
ERROR: File WORK.ABC_20210456.DATA does not exist.
ERROR: File WORK.ABC_20210457.DATA does not exist.
ERROR: File WORK.ABC_20210458.DATA does not exist.
ERROR: File WORK.ABC_20210459.DATA does not exist.
ERROR: File WORK.ABC_20210460.DATA does not exist.
ERROR: File WORK.ABC_20210461.DATA does not exist.
ERROR: File WORK.ABC_20210462.DATA does not exist.
ERROR: File WORK.ABC_20210463.DATA does not exist.
ERROR: File WORK.ABC_20210464.DATA does not exist.
ERROR: File WORK.ABC_20210465.DATA does not exist.
ERROR: File WORK.ABC_20210466.DATA does not exist.
ERROR: File WORK.ABC_20210467.DATA does not exist.
ERROR: File WORK.ABC_20210468.DATA does not exist.
ERROR: File WORK.ABC_20210469.DATA does not exist.
ERROR: File WORK.ABC_20210470.DATA does not exist.
ERROR: File WORK.ABC_20210471.DATA does not exist.
ERROR: File WORK.ABC_20210472.DATA does not exist.
ERROR: File WORK.ABC_20210473.DATA does not exist.
ERROR: File WORK.ABC_20210474.DATA does not exist.
ERROR: File WORK.ABC_20210475.DATA does not exist.
ERROR: File WORK.ABC_20210476.DATA does not exist.
ERROR: File WORK.ABC_20210477.DATA does not exist.
ERROR: File WORK.ABC_20210478.DATA does not exist.
ERROR: File WORK.ABC_20210479.DATA does not exist.
ERROR: File WORK.ABC_20210480.DATA does not exist.
ERROR: File WORK.ABC_20210481.DATA does not exist.
ERROR: File WORK.ABC_20210482.DATA does not exist.
ERROR: File WORK.ABC_20210483.DATA does not exist.
ERROR: File WORK.ABC_20210484.DATA does not exist.
ERROR: File WORK.ABC_20210485.DATA does not exist.
ERROR: File WORK.ABC_20210486.DATA does not exist.
ERROR: File WORK.ABC_20210487.DATA does not exist.
ERROR: File WORK.ABC_20210488.DATA does not exist.
ERROR: File WORK.ABC_20210489.DATA does not exist.
ERROR: File WORK.ABC_20210490.DATA does not exist.
ERROR: File WORK.ABC_20210491.DATA does not exist.
ERROR: File WORK.ABC_20210492.DATA does not exist.
ERROR: File WORK.ABC_20210493.DATA does not exist.
ERROR: File WORK.ABC_20210494.DATA does not exist.
ERROR: File WORK.ABC_20210495.DATA does not exist.
ERROR: File WORK.ABC_20210496.DATA does not exist.
ERROR: File WORK.ABC_20210497.DATA does not exist.
ERROR: File WORK.ABC_20210498.DATA does not exist.
ERROR: File WORK.ABC_20210499.DATA does not exist.
ERROR: File WORK.ABC_20210500.DATA does not exist.
15   run;

Re: How to create a macro variable that will take a range of date values?

Patrick — Tue, 27 Apr 2021 21:12:19 GMT

@Shradha1

You have accepted my post as solution but as @ballardw demonstrates it's actually not working over month boundaries.

You need to create a list of the tables using code as provided by @Kurt_Bremser

Re: How to create a macro variable that will take a range of date values?

ChrisNZ — Tue, 27 Apr 2021 21:46:10 GMT

Another way:

data WANT;
  set 
  %macro loop;
    %local i;
    %do i = &sd %to &ed;
      %if %sysfunc(exist(ABC_&i)) %then ABC_&i;
    %end;
  %mend;
  %loop;
run;

Re: How to create a macro variable that will take a range of date values?

ChrisNZ — Tue, 27 Apr 2021 21:48:39 GMT

@Patrick Won't this will create an error for missing days like day 99?

Re: How to create a macro variable that will take a range of date values?

ChrisNZ — Tue, 27 Apr 2021 21:50:50 GMT

@Shradha1

Please change the chosen solution to a solution that works.

And don't choose one before trying 🙂