BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

Variables RECEIVABLE_START_DATE_SK and RECEIVABLE_END_DATE_SK  has values like -1, 20171212, 20180511...and my client wants me to convert this data which has format YYYY-MM-DD. In order to achieve this, I've applied the format yymmdd10. as I mentioned below, but data is not converting to the format as I thought. What might be the likely cause for the issue? Seems I've to switch to other format or there is an issue with the data as it has value like -1.

 

 

data test;
set F_PRM_WRITTEN_PREMIUM;
format RECEIVABLE_START_DATE_SK RECEIVABLE_END_DATE_SK format yymmdd10.;
run;

 

8 REPLIES 8
Kurt_Bremser
Super User

Reread the documentation of SAS date values, and see how your current numeric values fit in.

For the umpteenth time (and I am strongly disappointed that we have to tell you time and again), SAS date values are

COUNTS OF DAYS FROM A GIVEN STARTING POINT

(the starting point being 1960-01-01)

so -1 points to 1959-12-31, while 20171212 points to a date 20 million days from 1960. Which points to the year 55263, which is well outside the range SAS is prepared for.

 

You need to make decisions which values are valid (I have a suspicion that -1 should end up as missing), and convert them (using put(input() with proper (in)formats) to date values which are displayed correctly with SAS date formats.

ie to convert the number 20171212 to a SAS date value, do

correct_date = input(put(wrong_date,8.),yymmdd8.);
format correct_date yymmddd10.;

BTW this statement

format RECEIVABLE_START_DATE_SK RECEIVABLE_END_DATE_SK format yymmdd10.;

will probably cause a NOTE in the log that variable format is uninitialized.

 

Tom
Super User Tom
Super User

It looks like your variables do not have dates in them.  A value of -1 is a valid date. Since SAS start numbering days with zero representing the first day of 1960 the value -1 represents the last day of 1959.

A value like 20,171,212 would represent a date in the year 57,185 and the YYMMDD format does not work when the value needs 5 digits for the year. 

154   data _null_;
155     do date=-1, 20171212, 20180511 ;
156       est_year=1960+floor(date/365.25);
157       put date comma11. +1 est_year comma7. +1 date yymmdd10. ;
158     end;
159   run;

         -1   1,959 1959-12-31
 20,171,212  57,185 **********
 20,180,511  57,211 **********

Convert the numbers into strings and then convert the strings into dates using a proper INFORMAT.  Then you can attach any date format you want to the value.

So assuming that somehow your original dataset mistakenly created numbers instead of dates you can just convert the values to strings using a PUT() function call and convert the string to a date using an INPUT() function call with the YYMMDD informat. Since dates are stored in numeric variables there is no need to make new variables.

I assume the -1 values are not intended to mean 31DEC1959 so I have told it to test for that value and convert those to missing.

Since I used an array you could use the same logic for as many "date" variables as your source data has.

 

data test;
  set F_PRM_WRITTEN_PREMIUM;
  array _dates_ RECEIVABLE_START_DATE_SK RECEIVABLE_END_DATE_SK ;
  do over _dates_;
    if _dates_=-1 then _dates_=. ;
    else _dates_ = input(put(_dates_,z8.),yymmdd8.); 
  end;
  format RECEIVABLE_START_DATE_SK RECEIVABLE_END_DATE_SK yymmdd10.;
run;

 

PS If you don't remember how the DO OVER statement works you are free to convert the code to use indexed array references instead. But if you do you will want to make sure to use a name for the index variable that does not already exist in your dataset and also remember to drop the extra (unnecessary , unwanted) variable.

 

 

 

Babloo
Rhodochrosite | Level 12

I've one more question. Assume if I've data like 20170823 for the numeric variable X and I want to convert it to 2017-08-23. I tried to apply the format yymmdd10. to the variable X but I end up with **********

Kurt_Bremser
Super User

@Babloo wrote:

I've one more question. Assume if I've data like 20170823 for the numeric variable X and I want to convert it to 2017-08-23. I tried to apply the format yymmdd10. to the variable X but I end up with **********


For a start, read my post and try to grasp its meaning.

Patrick
Opal | Level 21

@Babloo

You really need to spend the time to understand how Date, Datetime and Time works in SAS as else you'll never succeed. Here two links to give you a start.

http://documentation.sas.com/?docsetId=lrcon&docsetTarget=p1wj0wt2ebe2a0n1lv4lem9hdc0v.htm&docsetVer...

http://www2.sas.com/proceedings/sugi24/Coders/p073-24.pdf

 

A numeric value like "20170823" is NOT representing a SAS Date value the way you appear to believe and though the SAS Date format applied on it doesn't return what you expected.

Read the docu links I've posted. If you don't understand what's explained there come back here and ask targeted questions.

ruchi11dec
Obsidian | Level 7

I think, you must query, regarding the data, what I think is that the value -1 corresponds to 31dec1959, but the other values as you mentioned are the dates in the formats yyyymmdd. 

ballardw
Super User

@Babloo wrote:

Variables RECEIVABLE_START_DATE_SK and RECEIVABLE_END_DATE_SK  has values like -1, 20171212, 20180511...and my client wants me to convert this data which has format YYYY-MM-DD. In order to achieve this, I've applied the format yymmdd10. as I mentioned below, but data is not converting to the format as I thought. What might be the likely cause for the issue? Seems I've to switch to other format or there is an issue with the data as it has value like -1.

 

 

data test;
set F_PRM_WRITTEN_PREMIUM;
format RECEIVABLE_START_DATE_SK RECEIVABLE_END_DATE_SK format yymmdd10.;
run;

 


I would say go back to the point when the original SAS data set is created. That is the time to address whether you are reading values correctly (date informats for example).

 

One would hope that you and your client have an agreement about what the content of files provided looks like (layout) and content (text, numeric, date) and such.

 

 

Patrick
Opal | Level 21

@Babloo wrote:

Variables RECEIVABLE_START_DATE_SK and RECEIVABLE_END_DATE_SK  has values like -1, 20171212, 20180511...and my client wants me to convert this data which has format YYYY-MM-DD. In order to achieve this, I've applied the format yymmdd10. as I mentioned below, but data is not converting to the format as I thought. What might be the likely cause for the issue? Seems I've to switch to other format or there is an issue with the data as it has value like -1.

 

 

data test;
set F_PRM_WRITTEN_PREMIUM;
format RECEIVABLE_START_DATE_SK RECEIVABLE_END_DATE_SK format yymmdd10.;
run;

 


 

And reading your original post once more:

Your variable names end with _SK. That's a naming convention - at least in SAS data models - often used for Surrogate Key columns. Also a possible value of -1 points to this.

If I'm right then you're dealing with a Fact table and there must be somewhere a Dimension table with a name like Date_Dim. -1 in the keys is often used for a default value in case of missing.

Find the "Date_Dim" table and then look-up the date over the _SK. If you need only the date then you could also generate a format using "Date_Dim" using _SK as the value and the actual Date column as the label and then just apply this format on the _SK's to get the dates. 

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
  • 8 replies
  • 1228 views
  • 3 likes
  • 6 in conversation