Automatic selection of periods/date

[Chris_LK_87]

Hello, 

I have a dataset that have three variables: period (datevar.), sales and depart. The time series starts in 2021-01-01 and grows every month when a new month’s salesfigures is added.

For now I am using a macro that I have to update every month; eg. current date minus two months.

I would like to create a macro, with TODAY Function or something else, so I can automatically select periods that is the current date minus two months without needing to updated the macro every month.

data have;

;

input period :yymmdd10. sales depart$;

format period yymmddd10.;

datalines;

2021-01-01 200 Sales

2021-02-02 150 Sales

2021-03-01 320 Sales

2021-04-01 220 Sales

2021-05-01 250 Sales

2021-06-01 320 Sales

2021-07-01 400 Sales

2021-08-01 550 Sales

2021-09-01 900 Sales

2021-10-01 550 Sales

2021-11-01 850 Sales

2021-12-01 750 Sales

2022-01-01 650 Sales

;

run;

%let time=('01jan2021'd<=period<='01jan2022'd);

data out;

set have;

if &time.;

run;

[ballardw]

Your example is not selecting "the current date minus two months" but apparently a previous YEAR.

Two months might be:

 
data out;
set have;
if intck('month',period,today() ) le 2;
run;

You ask this question in MAR 2022 and only provide one value within 2 months for example data so there is only one record.

[Chris_LK_87]

Thank you!

[Astounding]

SAS should be able to handle this without creating a macro.  You haven't described the end of the time period, so I'll assume you want 12 months in total:

data want;
   set have;
   if intnx('month', today(), -2) <= period  < intnx('month', today(), 9);
run;

[Chris_LK_87]

It could be longer, 12 months is onlys an example.

[Kurt_Bremser]

This creates a macro variable indicating the beginning of the next-to-last month:

%let cutoff = %sysfunc(intnx(month,%sysfunc(today()),-2,b));

This can easily be used in comparisons with SAS date values:

where date ge &cutoff.;

[Chris_LK_87]

Hello, 

I asked a question earlier about this issue. I will try to rephrase my question.

I have a dataset that have three variables: period (datevar.), sales and depart. The time series starts in 2021-01-01 and grows every month when a new month’s sales figures is added. The data for the latest months (in this example 2022-02-01 and 2022-03-01) is not reliable. 

For now, I am using a macro that I must update every month. With the macro I select the starting date and the end date (current date minus two months.) 

I would like to create a macro, with TODAY Function or something else, so I can automatically select starting date (2021-01-01) and end date (current date minus two months: 2021-01-01). I would like to achieve this without needing to update the macro every month. 

data have;

;

input period :yymmdd10. sales depart$;

format period yymmddd10.;

datalines;

2021-01-01 200 Sales

2021-02-02 150 Sales

2021-03-01 320 Sales

2021-04-01 220 Sales

2021-05-01 250 Sales

2021-06-01 320 Sales

2021-07-01 400 Sales

2021-08-01 550 Sales

2021-09-01 900 Sales

2021-10-01 550 Sales

2021-11-01 850 Sales

2021-12-01 750 Sales

2022-01-01 650 Sales

2022-02-01 100 Sales

2022-03-01 25   Sales

;

run;

%let time=('01jan2021'd<=period<='01jan2022'd);

data out;

set have;

if &time.;

run;

data want;

;

input period :yymmdd10. sales depart$;

format period yymmddd10.;

datalines;

2021-01-01 200 Sales

2021-02-02 150 Sales

2021-03-01 320 Sales

2021-04-01 220 Sales

2021-05-01 250 Sales

2021-06-01 320 Sales

2021-07-01 400 Sales

2021-08-01 550 Sales

2021-09-01 900 Sales

2021-10-01 550 Sales

2021-11-01 850 Sales

2021-12-01 750 Sales

2022-01-01 650 Sales

[Kurt_Bremser · Accepted solution]

I moved your second question back in here, as it deals with the same basic issue.

So you want to automatically define a time span, starting at the beginning of two months and one year before now, and ending at the beginning of two months before now?

Create the start and end points:

%let start = %sysfunc(intnx(month,%sysfunc(today()),-14,b));
%let end = %sysfunc(intnx(month,%sysfunc(today()),-2,b));

Then use them to subset:

data want;
set have;
where &start. le period le &end.;
run;

[Chris_LK_87]

Thank you!