topic Re: How to use macro variables to retrieve data of a specific time window in SAS Programming

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

yming954 — Wed, 05 Jun 2024 14:04:17 GMT

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.

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

ballardw — Wed, 05 Jun 2024 14:14:03 GMT

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.

Re: How to use macro variables to retrieve data of a specific time window

PaigeMiller — Wed, 05 Jun 2024 15:03:45 GMT

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

%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.

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

yming954 — Wed, 05 Jun 2024 17:51:53 GMT

Thank you very much for your advice @ballardw

My variable contains date format like ddmmmyyyy (date9.)

Will that work using the first code? Thanks

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

ballardw — Wed, 05 Jun 2024 18:11:55 GMT

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.

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

Tom — Wed, 05 Jun 2024 20:07:31 GMT

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

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

yming954 — Wed, 05 Jun 2024 20:25:00 GMT

@ballardw

the date format is

So what should I do to fix the macros?

Re: How to use macro variables to retrieve data of a specific time window

PaigeMiller — Wed, 05 Jun 2024 21:04:33 GMT

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

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

ballardw — Wed, 05 Jun 2024 21:14:33 GMT

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?

Re: How to use macro variables to retrieve data of a specific time window

yming954 — Thu, 06 Jun 2024 13:46:12 GMT

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

Re: How to use macro variables to retrieve data of a specific time window

PaigeMiller — Thu, 06 Jun 2024 13:53:38 GMT

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.

Re: How to use macro variables to retrieve data of a specific time window

ballardw — Thu, 06 Jun 2024 14:46:46 GMT

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.