topic Re: Macro for character_date range in SAS Programming

Macro for character_date range

Chris_LK_87 — Sun, 19 Sep 2021 14:17:39 GMT

I have a dataset that have two variables, period (character) and sales. The time series starts in Januari 2021 and grows every month when a new month’s salesfigures is added.
The variable period contains information about month and year. Even months that have not yet occurred are in the dataset.

I would like to create a macro so I can select periods that have occurred. That is possible to update very month.

I have tried this:

%Let t_period='Apr_2021' <=period<= 'Sep_2021';

Not sure if I am doing it right.

data have;
;
input period$ sales;
datalines;
Jan_2021 100
Feb_2021 200
Mar_2021 150
Apr_2021 320
May_2021 220
Jun_2021 250
Jul_2021 320
Aug_2021 400
Sep_2021 550
Oct_2021 .
Nov_2021 .
Dec_2021 .
Jan_2022 .
Feb_2022 .
Mar_2022 .
;
run;

data want;
;
input Period$ Sales;
datalines;
Jan_2021 100
Feb_2021 200
Mar_2021 150
Apr_2021 320
May_2021 220
Jun_2021 250
Jul_2021 320
Aug_2021 400
Sep_2021 550
;
run;

Thanks

Re: Macro for character_date range

sbxkoenk — Sun, 19 Sep 2021 14:41:19 GMT

Hello,

Never use CHAR values for your date(time)s!

You are unable to use comparison operators then (as well as useful functions like INTCK and INTNX and many others).

See below!

data have;
input period $ sales;
datalines;
Jan_2021 100
Feb_2021 200
Mar_2021 150
Apr_2021 320
May_2021 220
Jun_2021 250
Jul_2021 320
Aug_2021 400
Sep_2021 550
Oct_2021 .
Nov_2021 .
Dec_2021 .
Jan_2022 .
Feb_2022 .
Mar_2022 .
;
run;

data want;
 set have;
 ProperDateTemp='01'!!scan(period,1,'_')!!scan(period,2,'_');
 ProperDateNume=input(ProperDateTemp,date9.);
 ProperDateNume=INTNX('MONTH',ProperDateNume,0,'END');
 if ProperDateNume < today() then output;
 format ProperDateNume date9.; /* You can also try MONYY7. to avoid days to be displayed */
run;
/* end of program */

I transform your char value to the last day of the month, using a proper type (numeric) and (in)format (date9.).

Koen

Re: Macro for character_date range

Tom — Sun, 19 Sep 2021 15:10:12 GMT

Convert the string into a DATE to test.

%let t_period=
  '01APR2021'd <= input(cats('01',period),date11.) <= '01SEP2021'd
;

If you want the boundaries to be dynamic is %SYSFUNC() and INTNX().

%let t_period=
  %sysfunc(intnx(month,%sysfunc(date()),-5))
 <= input(cats('01',period),date11.) 
 <= %sysfunc(intnx(month,%sysfunc(date()),0))
;

Re: Macro for character_date range

PaigeMiller — Sun, 19 Sep 2021 16:22:41 GMT

I have tried this:

%Let t_period='Apr_2021' <=period<= 'Sep_2021';

As others have said, using character strings to represent calendar values makes the process incredibly difficult, if not impossible. The code above will also find Nov_2021 (and Nov_2020) as between 'Apr_2021' and 'Sep_2021'. Why? Because character strings are evaluated alphabetically, and alphabetically Nov is between Apr and Sep.

As other have provided code where your dates are represented as numbers instead of character strings, I don't need to provide code here.

Re: Macro for character_date range

Astounding — Sun, 19 Sep 2021 18:42:28 GMT

Is it too late to change your data? As others have shown, it's a somewhat complex problem with data in its current form. However, it could be a simple problem. Change from:

Jan_2021
Feb_2021
Mar_2021

Instead, use:

2021_01
2021_02
2021_03

Then the comparisons will be extremely simple. As an added bonus, sorting the data properly becomes much easier.

Re: Macro for character_date range

PaigeMiller — Sun, 19 Sep 2021 22:24:49 GMT

Yes, text string such as '2021_01' will work if your only interest is sorting or boolean comparisons. Actual numeric SAS dates still work better if you want to do arithmetic (such as, what month was 18 months ago?) and works just as well as text strings for sorting or boolean comparisons. So, actual numeric SAS dates work in all situations; text strings do not. Thus I would recommend using actual numeric SAS dates.

Re: Macro for character_date range

Kurt_Bremser — Tue, 21 Sep 2021 09:41:31 GMT

On top of what the others already said, a SAS date can be stored in just 4 bytes of data (use

length date 4;

), so you need only half the place of your current strings; with lots of dates in your data, this means less I/O and therefore speedier programs.

Do this first:

data better;
set have (rename=(period=period_char));
length period 4;
period = input(period_char,monyy8.);
format period yymmd7.;
drop period_char;
run;

Then the WHERE is easy and works:

data want;
set better;
where '01Apr2021'd <= period <= '01Sep2021'd;
run;