topic SQL issue with dates. in SAS Programming

SQL issue with dates.

rohitguptaecb — Sat, 06 Apr 2013 09:35:18 GMT

I have a dataset like this.

Id Sal date

1 100 201108

2 200 201003

3 500 201304

4 300 200904

5 1000 200608

6 450 201201

Here, date is a variable that has numeric values. First four digits indicates year and last two digits indicates month. Now i want those observations whose date are between today and same day teo years ago.

for ex between 6Apr2011 and 6Apr2013 .

How can we fetch such result .. Plz help

Re: SQL issue with dates.

Patrick — Sat, 06 Apr 2013 10:13:57 GMT

You're having only year and month as a start so best you can do is select on a monthly level.

First convert the number to a SAS date, eg.

date=input(put(date,6.),yymmn6.);

The SAS date you're getting this way is always on the first of the month.

Then use in your selection intnx() to get the range right, eg.

where today()>= date > intnx('year',today(),-1,'s')

Re: SQL issue with dates.

PGStats — Sat, 06 Apr 2013 12:42:13 GMT

where date between intnx('year', today(), -2, 's') and today()

Re: SQL issue with dates.

Tom — Sat, 06 Apr 2013 16:41:37 GMT

Not sure what SQL has to do with it. You might also consider converting SAS dates into to your YYYYMM decimal coding values. If the table is large this has to advantage of not having to make conversions on every value in the table in order to test it.

To get today's date you can use TODAY() function or &SYSDATE9 macro variable. (difference is that &SYSDATE9 is set when the SAS session starts).

In macro code you could do:

%let today = %sysfunc(today(),yymmn6.);

%let twoyearsago = %eval(&today - 200) ;

Then you can do something like:

select * from have where date between &twoyearsago and &today