if vs where

Accepted Solution Solved
Reply
Valued Guide
Posts: 858
Accepted Solution

if vs where

Some code I'm working with:

data wr_cust_fact(keep=cust_power_id record_date CUST_PD_REQUIRED_FLG);

format record_date mmddyy10.;

set wbdw.customer_fact(obs=10000);

record_date = input(put(time_key_cd,8.),yymmdd8.);

where substrn(time_key_cd,1,4) > 2014 and

      substrn(time_key_cd,5,2) > 3;

run;

This is pulling from a large table that has no date field that I can use.  I'd prefer to use 'where' but not sure how that will work for this situation.  If I use 'if record_date > '31MAR2014'd' it takes an excessive amount of time to pull the entire dataset in.  I'm trying to use substrn but that doesn't seem to be working the way I want it to either.  Without the where clause and using the obs this takes less than a second but with the where clause it isn't finishing at all, I keep killing it before I get any output because of the time.  Can anyone see a problem or make any suggestions?

Thanks,

Mark


Accepted Solutions
Solution
‎04-10-2015 10:45 AM
Super User
Posts: 11,134

Re: if vs where

If your time_key_cd records are numeric and look like 20140215 you might be better off with

where=(time_key_cd > 20140400) as a dataset option to find records > 31Mar2014:

set wbdw.customer_fact(obs=10000  where=(time_key_cd > 20140400));

View solution in original post


All Replies
Super User
Posts: 19,171

Re: if vs where

Wouldn't the substring function return characters variables?

Are you sure that your number is a number and not a numeric SAS date?

Super User
Posts: 5,369

Re: if vs where

IF and WHERE work differently when OBS= is in effect.  WHERE is going to keep looking through the data set until it finds 1000 observations that meet the WHERE condition.  That could be millions of observations ... it won't stop until it finds 1000.  IF will look through the first 1000 only, and take those that satisfy the IF condition.

Solution
‎04-10-2015 10:45 AM
Super User
Posts: 11,134

Re: if vs where

If your time_key_cd records are numeric and look like 20140215 you might be better off with

where=(time_key_cd > 20140400) as a dataset option to find records > 31Mar2014:

set wbdw.customer_fact(obs=10000  where=(time_key_cd > 20140400));

Valued Guide
Posts: 858

Re: if vs where

Seems so obvious now.  Not sure why I didn't see it that way.

Thanks,

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 235 views
  • 0 likes
  • 4 in conversation