Hi all, I am pretty new to using macros and proc sql so forgive me if my code is completely off base, but what I am trying to do is take some dates from an sql table and put them into 'batches' based on the week of the date. E.g. the most recent week would=1, the previous week=2 etc. I need this to be dynamic so that I can run it each week to get the most recent weeks output. I think my main issue is sas is not recognizing my macro dates as dates. Using different date formats hasn't helped and neither has taking out the quotes in my symput statements. I am using SAS EG. Any guidance is appreciated, thank you. proc sql; create table prep as select datepart(createdte) format mmddyy10. as dateloaded from table quit; proc sql; create table dates as select distinct dateloaded from prep order by dateloaded desc; quit; output ex: want to add: dateloaded batch 01/24/2019 1 01/18/2019 2 01/10/2019 3 01/04/2019 4 01/03/2019 4 12/21/2018 5 12/20/2018 5 %macro loop(); %global i ; %do i=1 %to 13 ; /*looping through the past 13 weeks (current week plus past 12 weeks/3months) to compare current week to weekly averages over past 3 months*/ data _null_; call symput('startdt',"'"||put(intnx('day',intnx('week',today(),-&i.,'b'),0),yymmdd10.)||"'");/*Sunday SQL date*/ call symput('enddt',"'"||put(intnx('day',intnx('week',today(),-&i.,'b'),6),yymmdd10.)||"'"); /*Saturday SQL date*/ run; %week (&startdt., &enddt., &i.); %end ; %mend loop; %macro week(startdt, enddt, i); %put &startdt &enddt &i; data batches; set dates; if dateloaded>= &startdt. and dateloaded <= &enddt.; batch = &i.; run; proc append base=dates data=batches_&i.; %mend week; %loop(); error ex: NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 49:2 49:2 NOTE: Invalid numeric data, '2019-01-13' , at line 49 column 2.
... View more