Issues Using Macro Do Loops and SQL Dates

[stage_4]

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.

[PaigeMiller]

Macro variables should not be formatted with date formats or with datetime formats. Leave them unformatted.

Then, your line

if dateloaded>= &startdt. and dateloaded <= &enddt.;

ought to work without errors.

Also, when running macros, use OPTIONS MPRINT; before you execute the macro, this will generate much more meaningful information in the log.

When asking for help here, do not show us PARTIAL log files. Click on the {i} icon and then paste the relevant parts of the log file (usually at least the entire PROC or entire DATA step where the error is, sometimes more is needed). The information you provided from the log is meaningless in its current form.

[SuryaKiran]

Hello,

Welcome to SAS Community!

Your formatting the date values as character. i.e. SAS dosen't recognize when you give date as '2019-01-25', it will treat as a character when expecting a date. You can give a SAS numeric date value (Number of days starting 1st jan 1960) 

example:

%let today=%sysfunc(today());
%put Numeric SAS date value for &=today;

or you need to tell sas that your giving Date values by '25JAN2019'D

You need to convert the code like: 

Remove PUT & format

data _null_;
call symput('startdt',intnx('day',intnx('week',today(),-1.,'b'),0));/*Sunday SQL date*/
call symput('enddt',intnx('day',intnx('week',today(),-1,'b'),6)); /*Saturday SQL date*/
run;

%put &startdt &enddt;

[Tom · Accepted solution]

NOTE: Invalid numeric data, '2019-01-13' , at line 49 column 2.

That message seems pretty clear.  A value with quotes around it or a value with hyphens in it is a string, not a number.

SAS stores dates as the number of days since 1960.

If you want to generate a specific date you in a human readable form you can use a date literal.  Those are quoted text that the DATE informat can convert followed by the letter D.  Examples:

"01JAN2019"d
'01-jan-2019'D

Since it looks like you are generating macro variables to use to compare the date values then the easiest thing is to just leave the values as the number of days.

data _null_;
  call symputx('startdt',intnx('week',today(),-&i.,'b'));
  call symputx('enddt',intnx('week',today(),-&i.,'e'));
run;

%week (&startdt., &enddt., &i.);

If you did want to generate more human readable format for the macro variables then use the DATE9 format and add the quotes and D suffix.

You could add them in the macro call rather than in the macro variables themselves.

data _null_;
  call symputx('startdt',put(intnx('week',today(),-&i.,'b'),date9.);
  call symputx('enddt',put(intnx('week',today(),-&i.,'e'),date9.);
run;

%week ("&startdt"d, "&enddt"d, &i.);

Or included them in the macro variables themselves.

data _null_;
  call symputx('startdt',quote(put(intnx('week',today(),-&i.,'b'),date9.))||'d');
  call symputx('enddt',quote(put(intnx('week',today(),-&i.,'e'),date9.))||'d');
run;

Example:

1001  %let i=0;
1002  data _null_;
1003    call symputx('startdt',quote(put(intnx('week',today(),-&i.,'b'),date9.))||'d');
1004    call symputx('enddt',quote(put(intnx('week',today(),-&i.,'e'),date9.))||'d');
1005  run;

NOTE: DATA statement used (Total process time):
      real time           0.10 seconds
      cpu time            0.01 seconds


1006  %put &=i &=startdt &=enddt ;
I=0 STARTDT="20JAN2019"d ENDDT="26JAN2019"d

[stage_4]

Thank you this was very helpful! Here is my updated WORKING code:

options mcompile mlogic mprint;
%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',quote(put(intnx('week',today(),-&i.,'b'),date9.))||'d');/*Sunday SQL date*/
				call symput('enddt',quote(put(intnx('week',today(),-&i.,'e'),date9.))||'d'); /*Saturday SQL date*/
			run;
          	%week (&startdt., &enddt., &i.);
	%end ;
%mend loop;

%macro week(startdt, enddt, i);
data batches_&i;
set dates;
batch = &i.;
where dateloaded>= &startdt. and dateloaded <= &enddt.;
run;
proc append base=batches data=batches_&i.; run;

%mend week;

%loop(); 

[ballardw]

You may need to review more about dates in general before attempting to work with macros and dates.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.