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;
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;
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.
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;
It could be longer, 12 months is onlys an example.
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.;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.