BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Zachary
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

10 REPLIES 10
ballardw
Super User

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.

Zachary
Obsidian | Level 7

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.

ballardw
Super User

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?


Zachary
Obsidian | Level 7

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

ballardw
Super User

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.

Zachary
Obsidian | Level 7

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.

ballardw
Super User

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

Zachary
Obsidian | Level 7

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?

ballardw
Super User

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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 810 views
  • 3 likes
  • 2 in conversation