Help using Base SAS procedures

Cannot Figure Zero Observations Given a Date Range - Possibly an Issue of Permissions for User-Defined Libraries?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 115
Accepted Solution

Cannot Figure Zero Observations Given a Date Range - Possibly an Issue of Permissions for User-Defined Libraries?

l am having problems diagnosing what is going on with my data. Usually I select a date range directly after I read in some SAS data, and it works perfectly:

DATA POLICY;

  SET OraRef.SAS_POLICY;

  where (EFFECTIVE_DATE >= "01Jan2004"d and EFFECTIVE_DATE <= "31Dec2013"d) and (MULTI_STATE_FLAG = "N");

RUN;

Now I did a procedure to save the Policy data to my user-defined library, and now use the where:

data L_P1.PolicyData2012;

  set OraRef.SAS_POLICY;

run;

data POLICY;

  set L_P1.PolicyData2012 (keep = pol_phr_code pol_code pol_cmp_code MULTI_STATE_FLAG EFFECTIVE_DATE EXPIRE_DATE);

  where (EFFECTIVE_DATE >= "01Jan2012"d and EFFECTIVE_DATE <= "31Dec2012"d) and (MULTI_STATE_FLAG = "N");

run;

I already narrrowed down that the keep statement is not causing any problems. Also, pulling the dates via >= & <= versus using between is not an issue. It almost appears to be an issue of permissions, or something like that. I can open up the file without the data range. But when I experiment with the data range with a proc freq it again pulls zero observations. Any ideas? Thank you.

proc freq data = POLICY;

  tables MULTI_STATE_FLAG;

  where (EFFECTIVE_DATE between "01Jan2012"d and "31Dec2012"d) and (MULTI_STATE_FLAG = "N");

run;


Accepted Solutions
Solution
‎02-10-2015 03:52 PM
Super User
Posts: 10,552

Re: Cannot Figure Zero Observations Given a Date Range - Possibly an Issue of Permissions for User-Defined Libraries?

Houston we have a winner. I think. Your effective date is a datetime variable apparently in what you are displaying. Look at the format assigned. If it is some flavor of datetime18. or so then that is the cause of your problem.

Datetime variables are the number of seconds from 01Jan1960 and Date values are the number of days. Since there are something like 86400 seconds per day the numeric value difference is such that 31DEC2012, which would be 19358 is WAY less than the 1640995200 which is 01JAN2012:00:00:00.

That's why you are getting unacceptable responses from the year function. Try year(datepart(effective_date)). I bet you'll see a difference.

View solution in original post


All Replies
Super User
Posts: 10,552

Re: Cannot Figure Zero Observations Given a Date Range - Possibly an Issue of Permissions for User-Defined Libraries?

I would look at your input data with something like:

proc freq data=l_P1.PolicyData2013;

tables Effective_date*Multi_state_flag;

format Effective_date year4.;

run;

it may be the flag variable causing the problem such as "n" vs "N", leading space or no "N" values.

Frequent Contributor
Posts: 115

Re: Cannot Figure Zero Observations Given a Date Range - Possibly an Issue of Permissions for User-Defined Libraries?

Thank you very much for the suggestion. Unfortunately it does not appear to be the culprit.

But something is weird about my end date of <= "31Dec2012"d. If I thrown in the first date everything is ok. But having that 2nd date is somehow throwing things off? I even substituted the variable name for the beginning date into the 2nd one in case if I had any hidden characters. Nothing is working, so far.

Super User
Posts: 10,552

Re: Cannot Figure Zero Observations Given a Date Range - Possibly an Issue of Permissions for User-Defined Libraries?

Proc compare on L_P1.PolicyData2012 and  OraRef.SAS_POLICY perhaps.

You keep reusing the data set named POLICY so if there is other code you aren't showing use that use that name there may be an issue.

And are there any records for 2012 in the OraRef.SAS_POLICY data set?


Frequent Contributor
Posts: 115

Re: Cannot Figure Zero Observations Given a Date Range - Possibly an Issue of Permissions for User-Defined Libraries?

Trying all of your suggestions, thank you again.

At the bottom is a picture of my data if I comment out the date selection- maybe you see something in my code below. You will also notice the new way I did it by putting ED in the middle of the range:

data POLICY;

  set L_P1.PolicyData2012 (keep = pol_phr_code pol_code pol_cmp_code MULTI_STATE_FLAG EFFECTIVE_DATE EXPIRE_DATE);

  /* where ('01JAN2012'd <= EFFECTIVE_DATE <= '02JAN2012'd) and (MULTI_STATE_FLAG = "N"); */

run;

Also, thank you for introducing me to proc compare.

I am also inserting another screen image of how I tried to calculate the year off of the EFFECTIVE_DATE variable. You can see with all of the notes that I was not very successful.

Sorry that this one is dragging on. But thank you. I clearly need more help.

Capture.JPG

Capture2.JPG

Solution
‎02-10-2015 03:52 PM
Super User
Posts: 10,552

Re: Cannot Figure Zero Observations Given a Date Range - Possibly an Issue of Permissions for User-Defined Libraries?

Houston we have a winner. I think. Your effective date is a datetime variable apparently in what you are displaying. Look at the format assigned. If it is some flavor of datetime18. or so then that is the cause of your problem.

Datetime variables are the number of seconds from 01Jan1960 and Date values are the number of days. Since there are something like 86400 seconds per day the numeric value difference is such that 31DEC2012, which would be 19358 is WAY less than the 1640995200 which is 01JAN2012:00:00:00.

That's why you are getting unacceptable responses from the year function. Try year(datepart(effective_date)). I bet you'll see a difference.

Frequent Contributor
Posts: 115

Re: Cannot Figure Zero Observations Given a Date Range - Possibly an Issue of Permissions for User-Defined Libraries?

Thank you very much. It was able to precisely isolate the year I was looking for. Yay!

But now I am worried about how I have been pulling in the data and date ranges all of this time. Below is the code I use to generally pull in the data when working with our IT deparment:

proc sql;

CONNECT TO oracle( user=sas_app orapw="{sas002}F47DB14D49A3125D1A77A9662758292C10070A844C83FEB8424BEBA7" path="@test" );

EXECUTE ( execute uw_predict.fill_in_data(to_date('01/01/2004', 'MM/DD/YYYY'), to_date('12/31/2013', 'MM/DD/YYYY')) ) by oracle;

DISCONNECT FROM oracle;

But then I have been kick-starting my more targeted code with the following:

DATA POLICY;

  SET OraRef.SAS_POLICY;

  where (EFFECTIVE_DATE >= "01Jan2004"d and EFFECTIVE_DATE <= "31Dec2013"d) and (MULTI_STATE_FLAG = "N");

RUN;

Does that mean that I have been incorrectly pulling in dates? I know I am at the very least in the ballpark.

You are correct in that EFFECTIVE_DATE is DATETIME20.

Super User
Posts: 10,552

Re: Cannot Figure Zero Observations Given a Date Range - Possibly an Issue of Permissions for User-Defined Libraries?

Since I don't connect to Oracle I can't say. Depending on how often you run this process and how you pull the data from Oracle there may have been a change in the Oracle side of things such that a field that was previously a date is now a date-time. The underlying numbers of the date-times are such that if you had previously looked at Effective_date < "any likely date literal" you would have encountered an empty set such as this time.

Your code should work if you use "01Jan2012:00:00'dt and "31DEC2012:00:00"dt

Frequent Contributor
Posts: 115

Re: Cannot Figure Zero Observations Given a Date Range - Possibly an Issue of Permissions for User-Defined Libraries?

Thank you very much, again. I am working from home today (kind of sick) but will make some meeetings with the IT folks when I get back into the office.

Can you recommend a good site to make the distinction between date and date-time variables?

Super User
Posts: 10,552

Re: Cannot Figure Zero Observations Given a Date Range - Possibly an Issue of Permissions for User-Defined Libraries?

I would start with the online help under "date values" in the index. That should get you to the SAS help page "About SAS Date, Time and Datetime values". Then the date related functions.

BTW if you had previously been running your code with "01JAN2012"d <= Effective_date you likely didn't filter any records out.

Frequent Contributor
Posts: 115

Re: Cannot Figure Zero Observations Given a Date Range - Possibly an Issue of Permissions for User-Defined Libraries?

Thanks a bunch. Is a little comforting knowing that.

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 287 views
  • 3 likes
  • 2 in conversation