if vs where

Accepted Solution Solved
Reply
Valued Guide
Posts: 854
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
Grand Advisor
Posts: 10,210

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
Grand Advisor
Posts: 17,332

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?

Respected Advisor
Posts: 4,969

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
Grand Advisor
Posts: 10,210

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: 854

Re: if vs where

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

Thanks,

☑ This topic is SOLVED.

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

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