DATA Step, Macro, Functions and more

Using sysdate to capture previous quarters/years

Reply
Frequent Contributor
Posts: 143

Using sysdate to capture previous quarters/years

data _NULL_;

month = month(input("&sysdate9",date9.));

year = year(input("&sysdate9",date9.));

call symput('month',compress(put(month,best.)));

call symput('year',compress(put(year,best.)));

run;

Output is

44 %put &year;

SYMBOLGEN: Macro variable YEAR resolves to 2017

2017

45 %put &month;

SYMBOLGEN: Macro variable MONTH resolves to 11

Based on this month of November and this year.

I have a report that will require me to go back several quarters and years.  I have a start up program that will give me the desired time period so lets say I need to not only pull November 2017 but I need to pull another date, say June 30, 2017?  How can I put in code like above to get me the desired date.  Quarterly would be even better (ie jan-mar=qtr1,  apr-jun=qtr2 etx

Super User
Posts: 19,861

Re: Using sysdate to capture previous quarters/years

Use INTNX to increment the data the interval desired. It's not clear from your question exactly how you want that to work, so if you could post a more detailed example that would be helpful.

Frequent Contributor
Posts: 143

Re: Using sysdate to capture previous quarters/years

I guess my point is if I have a proc sql statement like this

month = month(input("&sysdate9",date9.));

year = year(input("&sysdate9",date9.));

call symput('month',compress(put(month,best.)));

call symput('year',compress(put(year,best.)));

 

proc sql;

create table test as

select id,

data_date

from test1

where year(data_date ) = 09 hard coded

and month(data_date) = 2017 hard coded

;quit;

sample output
id     data_date
01    30sep2017
02    30sep2017

The data_date field is a date9 field.  I want to dynamically(no hard code) to include previous quarters and dynamically list month, day or year.  For example I may have other data sets that must capture year = 2016 and month = 06

Is there a way to take the sysdate and have it show a different date that the current date???  I will resubmit this in another forum also

 

Super User
Posts: 5,516

Re: Using sysdate to capture previous quarters/years

One approach would be to remove &SYSDATE from the DATA step:

 

%let refdate = &sysdate9;

data _NULL_;

month = month(input("&refdate",date9.));

year = year(input("&refdate",date9.));

call symput('month',compress(put(month,best.)));

call symput('year',compress(put(year,best.)));

run;

 

Now if you want a different date, just change the first line, such as:

 

%let refdate = 30JUN2017;

 

Or, you might have automated logic that comes up with a way to replace REFDATE at the beginning of the program.

Frequent Contributor
Posts: 143

Sysdate to capture current and previous dates or quarters

data _NULL_;

month = month(input("&sysdate9",date9.));

year = year(input("&sysdate9",date9.));

call symput('month',compress(put(month,best.)));

call symput('year',compress(put(year,best.)));

run;

 

proc sql;

create table test as

select id,

data_date

from test1

where year(data_date ) = 09 hard coded

and month(data_date) = 2017 hard coded

;quit;

sample output
id     data_date
01    30sep2017
02    30sep2017

The data_date field is a date9 field.  I want to dynamically(no hard code) to include previous quarters and dynamically list month, day or year.  For example I may have other data sets that must capture year = 2016 and month = 06

Is there a way to take the sysdate and have it show a different date that the current date???  I will resubmit this in another forum also

 

Super User
Posts: 19,861

Re: Using sysdate to capture previous quarters/years

Please do not post multiple versions of the same question.

Ask a Question
Discussion stats
  • 5 replies
  • 99 views
  • 0 likes
  • 3 in conversation