Solved

Getting Beginning and Ending Date, last year, in SAS

Posted on 2014-03-14
6
504 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
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Subquery Syntax Assistance 13 50
Best way to use SSMS intellisense when querying 5 61
Oracle TEXT search question 9 48
sql query help 4 45
'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 …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

22 Experts available now in Live!

Get 1:1 Help Now