BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Chris_LK_87
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

8 REPLIES 8
ballardw
Super User

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
Quartz | Level 8
Thank you!
Astounding
PROC Star

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
Quartz | Level 8

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

Kurt_Bremser
Super User

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8
Thank you!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 903 views
  • 4 likes
  • 4 in conversation