loop macro for dates to run a SAS query

Question

I have a requirement to pull millions of data for more than 10,000 Ids at an event level for the last few years. I tried to use SAS macro to pull the data for each month however the query fails due to some threshold limits on the tables

 Data looks like this

 ID     Date      amount     seq
 101   1/15/2015   $100       a101
 101   1/17/2017   $100       b101
 101   3/15/2018   $50        c101
 101   3/21/2018   $200       d101

.......................
.......................
20001  2/15/2022   $100      a20001 
20001  3/18/2022   $10       b20001

My query

%macro  data(no,Start,End);
proc sql;
create table AllDataPoints as 
select * from connection to Teradata
(
 select dsitinct Id, seq, date, amount
 from table
 where date between &start and &end
);
quit;
%mend;

%data(1,'2015-01-01','2015-01-31');
%data(2,'2015-02-01','2015-02-28');
 ...
 ...
%data(12,'2015-12-01','2015-12-31');

I would repeat this for years 2015 thru 2022 but query fails due to some table limitations. Is there a way I can write a loop macro to repeat this for each day and later append all the data?

Thanks

Answer 1

If you wanted to do it by day, you can modify your macro as follows:

%macro data(Start, End, out);

    /* Convert to SAS date */
    %let start_date = %sysfunc(inputn(&start, yymmdd10.));
    %let end_date   = %sysfunc(inputn(&end, yymmdd10.));

    /* Delete previous table */
    proc delete data=&out;
    run;

    /* Loop through every day */
    %do i = &start_date %to &end_date;

        /* Convert to yyyy-mm-dd for Teradata */
        %let date  = %sysfunc(putn(&i, yymmddd10.) );

        proc sql;
            create table _tmp_data_ as 
            select * from connection to Teradata
            (
             select distinct Id, seq, date, amount
             from table
             where date = %tslit(&date)
            );
        quit;

        /* Append to a master dataset */
        proc append base=&out. data=_tmp_data_ force;
        run;

    %end;
%mend;

%data(2015-01-01, 2015-12-31, AllDataPoints);

The only change you need to make is to not use quotes when specifying your start/end dates, and specify a final output dataset.