BookmarkSubscribeRSS Feed
rohitguptaecb
Calcite | Level 5

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

3 REPLIES 3
Patrick
Opal | Level 21

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
Opal | Level 21

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

PG
Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 455 views
  • 1 like
  • 4 in conversation