topic Re: Automatic selection of periods/date in SAS Programming

Automatic selection of periods/date

Chris_LK_87 — Tue, 01 Mar 2022 15:15:08 GMT

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;

Re: Automatic selection of periods/date

ballardw — Tue, 01 Mar 2022 15:34:56 GMT

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.

Re: Automatic selection of periods/date

Astounding — Tue, 01 Mar 2022 15:38:03 GMT

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;

Re: Automatic selection of periods/date

Chris_LK_87 — Wed, 02 Mar 2022 07:12:53 GMT

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

Selection of dates

Chris_LK_87 — Wed, 02 Mar 2022 08:38:42 GMT

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

Re: Automatic selection of periods/date

Kurt_Bremser — Wed, 02 Mar 2022 08:59:12 GMT

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

Re: Selection of dates

Kurt_Bremser — Wed, 02 Mar 2022 09:10:18 GMT

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;

Re: Automatic selection of periods/date

Chris_LK_87 — Mon, 07 Mar 2022 07:11:52 GMT

Thank you!

Re: Selection of dates

Chris_LK_87 — Mon, 07 Mar 2022 07:12:12 GMT

Thank you!