CODEWITHSUNDEEP
loopsmacrossas
Jessica Warren
Jessica Warren

Reputation: 398

In SAS How do I iterate two dates and then insert them to a sql statements as an outer loop?

I want the outer loop to iterate the dates. The inner loop goes through different locations and then as the loop for location ends and then the date loop to iterate. The inner loop for location works fine.

I would like for the line that the where statement is to say

"where t1.Date BETWEEN 'date' and 'date1' " How do I go about creating that macro? Date and date1 must iterate at the same time in order to be two days away from each other at all times. 

PROC SQL;
   CREATE TABLE WORK.Report_dates&i AS /* every time the date changes/newtable*/
SELECT t1.ReportsID,
          t1.Type,
          t1.buys,
          t1.sells,
          t1.Age
          FROM work.candy as t1    
where (t1.Date BETWEEN '14Feb2016:0:0:0'dt AND '16Feb2016:0:0:0'dt) AND 
           (t1.Location = "SouthFlorida");
QUIT;


data list_of_dates;
format date date9.;
do Date = '01JUL2016'd to today();
output;
end;
run;

data list_of_dates1;
format date date9.;
do Date1 = '02JUL2016'd to today();
output;
end;
run;

The two loops work seperately when tested. But when put together I keep getting a sas error along the lines of " The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation marks. But I don't there are any unbalanced quotation marks, like I said they work separately when tested but not together. 

%macro queryLoop(startDate=, i=);

%macro loop(listA, listB);

%let p = %sysfunc(countw(&FACLNBR, %str( )));
%do i=1 %to &p;
    %let lista = %scan(&olista, &i, %str( ));
    %let listb = %scan(&olistb, &i, %str( ));


PROC SQL;
   CREATE TABLE WORK.Report_dates&i AS /* every time the date changes/newtable*/
SELECT t1.ReportsID,
          t1.Type,
          t1.buys,
          t1.sells,
          t1.Age
          FROM work.candy as t1    
where (datepart(t1.Date) BETWEEN &startDate AND &startDate+2 ) AND 
           (t1.Location = &list);
QUIT;

%end;
%mend; 

%loop(listA_1 listA_2, listB_1 listB_2);

%mend queryLoop;

data list_of_dates;
i=1;
format date date9.;
do Date = '01NOV2015'd to '01JAN2016'd;
string = catt('%queryLoop(startDate=', date, ', i=', i, ');');
call execute(string);
i+1;
output;
end;
run;

;*';*";*/;quit;run;

Upvotes: 1

Views: 526

Answers (1)

Reeza
Reeza

Reputation: 21294

I think this is what you're looking for, based on your previous question, this question and an educated guess. You need to first wrap the query in a macro and then when creating your dates, you can pass the call the macro with those dates directly using Call Execute.

See the code below. Note that I've commente out the call execute because I can't run the code, obviously.

%macro queryLoop(startDate=, i=);
PROC SQL;
   CREATE TABLE WORK.Report_dates&i AS /* every time the date changes/newtable*/
SELECT t1.ReportsID,
          t1.Type,
          t1.buys,
          t1.sells,
          t1.Age
          FROM work.candy as t1    
where (datepart(t1.Date) BETWEEN &startDate AND &startDate+2 ) AND 
           (t1.Location = "SouthFlorida");
QUIT;
%mend queryLoop;

data list_of_dates;
i=1;
format date date9.;
do Date = '01JUL2016'd to today();
string = catt('%queryLoop(startDate', date, ', i=', i, ');');
*call execute(string);
I+1;
output;
end;
run;

Upvotes: 1

Related Questions