Solved

Getting Beginning and Ending Date, last year, in SAS

Posted on 2014-03-14
6
520 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 11

Accepted Solution

by:
theartfuldazzler earned 350 total points
ID: 39929057
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
ID: 39929125
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
ID: 39929192
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
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 

Author Comment

by:Lee R Liddick Jr
ID: 39929257
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
ID: 39929334
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
ID: 39929466
That was perfect.  Found exactly what I was doing wrong and corrected.  Thank you so much!
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

632 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