Solved

Getting Beginning and Ending Date, last year, in SAS

Posted on 2014-03-14
6
499 Views
Last Modified: 2016-02-10
I need to create 'put' statements in order to get '01/01/previous year' and '12/31/previous year' for a program that is run on an annual basis so I don't need to manually change the year.

My sql code in the proc sql is this:
      AND EFF_DT <= '12/31/2013'
      AND (TRM_DT BETWEEN '01/01/2013' AND '12/31/2013'
            OR TRM_DT IS NULL)

This is also inside a macro and I'm not sure once I get the dates for the 'put' statement, what I need to change the hard coded dates to in order for the query to work.
0
Comment
Question by:Lee R Liddick Jr
  • 3
  • 2
6 Comments
 
LVL 11

Accepted Solution

by:
theartfuldazzler earned 350 total points
Comment Utility
Hi

the INTNX function is the one you want:


INTNX("year", TODAY() , -1, 'B')
INTNX("year", TODAY() , -1, 'E')

Will give you the "B"eginning of the previous year and the "E" of the previous year
0
 

Author Comment

by:Lee R Liddick Jr
Comment Utility
Not sure how to get this to function properly within my script though?  I just get an error:
ERROR: CLI describe error:  SQL0180N  The syntax of the string
       representation of a datetime value is incorrect.  

/*** GET PREVIOUS YEAR DATES ***/
data _null_;
      today = today();
      begdate = INTNX("year", TODAY() , -1, 'B');
      enddate = INTNX("year", TODAY() , -1, 'E');
      put begdate :date9.;
      put enddate :date9.;
run;
/* in the query */
      AND EFF_DT <= '&enddate'
      AND (TRM_DT BETWEEN '&begdate' AND '&enddate'
            OR TRM_DT IS NULL)
0
 
LVL 14

Assisted Solution

by:Aloysius Low
Aloysius Low earned 150 total points
Comment Utility
remove the quotation marks enclosing &enddate and &begdate should solve the problem.. that's because a single quotation mark treats the macro variable as a literal text while a double quotation mark causes the macro variable value into a string, which isn't valid here
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Lee R Liddick Jr
Comment Utility
Something else is going on.  It's inside a macro so I'm not sure if I set the values within the macro too?  No matter how I do it, I can't get it to process.

%macro hcx(st,rgn,dsname,file);

      AND EFF_DT <= &enddate
      AND (TRM_DT BETWEEN &begdate AND &enddate
            OR TRM_DT IS NULL)

%hcx(TX,%str(AND RGN_C = '1'),ctx,%str(filename) );
%hcx(FL,%str(AND RGN_C = '1'),cfl,%str('filename') );
0
 
LVL 14

Assisted Solution

by:Aloysius Low
Aloysius Low earned 150 total points
Comment Utility
to see what the macro is executing, execute the following code first:
options mprint;

to see what values the variables translate to, execute the following code first:
options symbolgen;

these should greatly help in the debugging process... at the moment with what was posted, it's almost impossible to help in troubleshooting...
0
 

Author Comment

by:Lee R Liddick Jr
Comment Utility
That was perfect.  Found exactly what I was doing wrong and corrected.  Thank you so much!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now