SQL issue with dates.

[rohitguptaecb]

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

[Patrick]

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')

[PGStats]

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

[Tom]

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