Solved

Getting Beginning and Ending Date, last year, in SAS

Posted on 2014-03-14
6
518 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Rewrite without the NULLIF 4 33
Estimating my database size 7 51
Need more granular date groupings 4 45
Sql Query to return all values in Capital letters 2 12
'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 …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 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