How to use macros to retrieve data of a specific time window

[yming954]

I have below Macros

*create macro variables for start and end dates for this cycle;

%if &season. = spring %then %do;

%let start = "01APR%eval(&CurrentYr. - 1)"d;

%let end = "30SEP%eval(&CurrentYr. - 1)"d;

%end;

%if &season. = fall %then %do;

%let start = "01OCT%eval(&CurrentYr. - 1)"d;

%let end = "31MAR&CurrentYr."d;

%end;

*Macro variables definitions - to use this macro, specify things in date9 - human readable

 To use the same values as inputs to the various %get macros, convert them - negates having to add input macro parameters ;

*start;

%let xstart = %sysfunc(putn(&start.,yymmddn8.));

*end;

%let xend = %sysfunc(putn(&end,yymmddn8.));

*cycle;

%let cyc = %sysfunc(putn(&start.,yymmn.));

*180 days prior to the earliest possible ddate - palliative;

%let xstart180 = %sysfunc(putn(&start.-180,yymmddn8.));

*put latest rpdb postal year into a macro variable

proc sql;

select max(year)

into :RPDByr

from rpdb;

quit;

*double check they resolve properly;

%put &xstart.;

%put &xend.;

%put &cyc.;

%put &xstart180.;

%put &RPDByr.;

I have a dataset called patient ( including one column named "date) and I want to retrieve the observation fall in the %xstart and %xend date, how do I write the code?

Thank you very much for your suggestions.

[ballardw]

Is the "date" variable supposed to contain SAS date values or character strings?

If date is SAS date values then it is very likely that your Xstart and Xend will almost never be appropriate.

If Date is just some random numeric value that happens to hold numbers like 20240516 then possibly:

where &xstart. le date le &xend. 

If date holds character strings like "20240516" then

where "&xstart." le date le "&xend." 

you don't mention if your "fall in the range" includes the end points or not. My example includes them.

[yming954]

Thank you very much for your advice @ballardw 

My variable contains date format like ddmmmyyyy (date9.)

Will that work using the first code? Thanks

[ballardw]

May be time to run Proc Contents on your data set and share the variable details. Tell us the actual name of the variable so we can be sure to understand which details are needed to answer that question.

---

@yming954 wrote:

Thank you very much for your advice @ballardw 

My variable contains date format like ddmmmyyyy (date9.)

Will that work using the first code? Thanks

---

If your variable is actually numeric with a date9 format attached then your &Xstart and &Xend variables are structured incorrectly and you would want to use the &start and &end in comparison as they should resolve to date values (hopefully , there's lots of details we don't have like &Season (which might have issues with case: Fall vs fall in the comparison for example) &Currentyr which I hope is 4 digits (2 digit years of 00 to 10 will fail creating invalid date literals

example:

%let CurrentYr= 09;
%let start = "01OCT%eval(&CurrentYr. - 1)"d;
%put &start.;

where Start is "01OCT8"d which is not a valid date literal. Year 00 would yield "01OCT-1"d which is also way off.

[yming954]

@ballardw 

the date format is 

So what should I do to fix the macros? 

[PaigeMiller]

So you are not using macros, you are using macro variables. Macro variables are not macros. ANd you don't have a valid SAS date variable, you have a DATETIME variable, which is different.

So the macro variables must contain valid DATETIME values.

%let start = "01APR%eval(&CurrentYr. - 1):00:00:00"dt;
%let end = "30SEP%eval(&CurrentYr. - 1):23:59:89"dt;

Note that the values contain hours:minutes:seconds and the DDMONYY part. Note the dt at the end indicating these are DATETIME values.

Now this should work

where dischargedate between &start and &end

[yming954]

493 %if &season. = spring %then %do;
494
495 %let start = "01APR%eval(&CurrentYr. - 1):00:00:00"dt;
496 %let end = "30SEP%eval(&CurrentYr. - 1):23:59:59"dt;
497
498 %end;
499
500 %if &season. = fall %then %do;
501
502 %let start = "01OCT%eval(&CurrentYr. - 1):00:00:00"d;
503 %let end = "31MAR(&CurrentYr.):23:59:59"dt;
504
505 %end;

506 *start;
507 %let xstart = %sysfunc(putn(&start.,yymmddn8.));
508
509 *end;
510 %let xend = %sysfunc(putn(&end,yymmddn8.));
ERROR: Argument 1 to function PUTN referenced by the %SYSFUNC or %QSYSFUNC macro function is
not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list.
Execution of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is
terminated.
511
512 *cycle;
513 %let cyc = %sysfunc(putn(&start.,yymmn.));
514
515 *180 days prior to the earliest possible ddate - palliative;
516 %let xstart180 = %sysfunc(putn(&start.-180,yymmddn8.));

@PaigeMiller  I am getting this errors, how could I fix it? Thank you very much for your guidance

[PaigeMiller]

You don't need &xstart and &xend any more, get rid of them, just use &start and &end.

Please remember that macro variables that contain date or datetime values which will be used for arithmetic or logical operations should NOT be formatted.

[ballardw]

First off look at this code carefully:

493 %if &season. = spring %then %do;
494
495 %let start = "01APR%eval(&CurrentYr. - 1):00:00:00"dt;
496 %let end = "30SEP%eval(&CurrentYr. - 1):23:59:59"dt;
497
498 %end;
499
500 %if &season. = fall %then %do;
501
502 %let start = "01OCT%eval(&CurrentYr. - 1):00:00:00"d;
503 %let end = "31MAR(&CurrentYr.):23:59:59"dt;

One of these things is not like the others.

Second, if the value resolves to a DATETIME value then almost any of the DATE format formats, such as YYMMDD will do one of two things, return the wrong value OR FAIL because datetimes have seconds as units and dates have days as units. So most datetime values overflow the allowed range of the date formats (which are currently only good to the year 9999)

Example

data junk;
  datetime = '01JAN1960:12:00:00'dt;
  put datetime= yymmdd10.;
run;

Check the log to see what is displayed.

So you don't want that format involved at all.

[ballardw]

To compare a variable that is a DATETIME as shown with a DATE value you would want to extract the date portion of the datetime variable

&start. le datepart(DischargeDate) le &end. 

should work if the &start and &end contain the correct date values.

Style choice: many of us do not like having the quotes as part of a macro variable value as when you read the code it may not show up that here &start is a quoted date literal, i.e, something like "01JAN2024"D .

If using date literals with macro variables I prefer to have the value in the macro variable as 01JAN2024 and then in the comparison code would use

"&start."d le Datepart(DischargeDate) ...

So when I see it I have a better idea what &start should look like. This allow use of the &start in places where I might want text such as

Title "XYZ Report starting &Start.";

Which would resolve to

XYZ Report starting 01JAN2024

where the title was used. (or as part of the name of an output file)

---

@yming954 wrote:

@ballardw 

the date format is 

So what should I do to fix the macros? 

---

[PaigeMiller]

When macro variables are to be used for arithmetic or logical operations, they SHOULD NOT be formatted. Maxim 28.

So

%let xstart = &start; %put &=xstart; /* Note: no format */
%let xend = &end; %put &=xend; /* Note: no format */

and then in SQL or a DATA step.

where date between &xstart and &xend

This assumes variable DATE has actual numeric SAS date values, but you really haven't specified that.

Also, you don't have macros here. You have macro variables, these are not the same. Best to call these macro variables.

[Tom]

So you have the following macro statements (there is no macro here since I do not see a %MACRO statement nor a %MEND statement).

%if &season. = spring %then %do;
  %let start = "01APR%eval(&CurrentYr. - 1)"d;
  %let end = "30SEP%eval(&CurrentYr. - 1)"d;
%end;
%if &season. = fall %then %do;
  %let start = "01OCT%eval(&CurrentYr. - 1)"d;
  %let end = "31MAR&CurrentYr."d;
%end;

Might be easier (and safer) to calculate the previous year number only once. So something like:

%let previousyr=%eval(&currentyr-1);
%if %upcase(&season) = SPRING %then %do;
  %let start = "01APR&previousyr"d;
  %let end = "30SEP&previousyr"d;
%end;
%else %if %upcase(&season) = FALLl %then %do;
  %let start = "01OCT&previousyr"d;
  %let end = "31MAR&CurrentYr."d;
%end;

Now that you have two macro variables with SAS date literals in them you can use them in your code anywhere that SAS would want a date value.

data want;
  set PATIENT;
  where date between &start and &end ;
run;

This assumes that DATE has numeric data values.  What display format is attached to the DATE variable does not matter.  It is the range of values that it contains that will impact whether the WHERE statement selects the observation or not.  The first day of November in the year 2000 is stored as the number 14,915 and displaying it with different formats does not change that fact.

2490  data test;
2491    date='01NOV2000'd;
2492    put (5*date) (=comma. +1 date9. +1 yymmdd10. +1 mmddyy10. +1 ddmmyy10.);
2493  run;

date=14,915  01NOV2000 2000-11-01 11/01/2000 01/11/2000