topic Re: Substr YYYY and MM from Sas date YYYYMM (edited) in SAS Programming

Substr YYYY and MM from Sas date YYYYMM (edited)

Cruise — Thu, 23 Mar 2017 11:40:12 GMT

I have a SAS date in format of YYYYMM as shown in my viewtable below. This YYYYMM "dob" variable is the truncation of original 8 digit SAS date for confidentiality purpose. So, I don't know how it originally looked like.

The final result that I would like to have is:

I tried several different ways and some of my unsuccessful attempts are here:

dob1 = put(dob, YYMMN6.); 
dob1 = input(dob, yymmn6.);
dy = substr(dob,5,4);
dm = substr(dob,7,2);

Jagadishkatam's suggested code below was resulted as shown:

dob1=put(dob,yymmn6.);
dy = year(dob1);
dm = month(dob1);

Result of above code:

I haven't tried substrn yet. I will soon.

This problem was previously discussed here. But it didn't work with my SAS9.4.

Any hints are appreciated.

Re: Substr YYYY and MM from Sas date YYYYMM

Jagadishkatam — Thu, 23 Mar 2017 02:24:29 GMT

We could use the year() and month() functions as below

dy=year(dob);
dm=month(dob);

Note: At the moment the date seem not right like the DOB of 189005 when converted to date9 format it is 23Jun2477. so it is a strange date. but if it is only for example then it is fine.

Re: Substr YYYY and MM from Sas date YYYYMM

Cruise — Thu, 23 Mar 2017 02:32:18 GMT

Hi Jagadishkatam,

The solution you suggested was resulted as shown below. Your observation using data9. is very interesting. it's not just for display. I really have to figure out this yyyymm date variable. Current yyyymm date I have is the result of a truncation of 8 digit SAS date, I think.

Re: Substr YYYY and MM from Sas date YYYYMM

Jagadishkatam — Thu, 23 Mar 2017 02:43:12 GMT

you could try the

using the put(dob,yymmn6.) it does retrieve the year and month. I am not sure what is the exact issue you have.

Re: Substr YYYY and MM from Sas date YYYYMM

Reeza — Thu, 23 Mar 2017 03:07:26 GMT

What does 189005 mean in terms of a date?

What part is the year, day and month?

Try SUBSTRN

year = substrn(old_var, 1, 4);
month = substrn(old_var, 5, 2);

Date_type =input( put(old_var, 6.), yymmn6.);
Year=year(date_type);
Month = month(date_type);

If this doesn't work post the exact code and log.

Re: Substr YYYY and MM from Sas date YYYYMM

Astounding — Thu, 23 Mar 2017 05:03:19 GMT

It would be helpful to have an example of the math. For example, show what YYYY and MM look like, an original DOB, and what the final result would look like after subtracting them.

Re: Substr YYYY and MM from Sas date YYYYMM

Cruise — Thu, 23 Mar 2017 11:40:51 GMT

just edited my original post as per your suggestion

Re: Substr YYYY and MM from Sas date YYYYMM

Cruise — Thu, 23 Mar 2017 11:45:30 GMT

Your code did the trick to a month. But years are still in 1809 something range. I will post exact details when i get back to office. I have a feeling that yours is getting close.

Re: Substr YYYY and MM from Sas date YYYYMM (edited)

Astounding — Thu, 23 Mar 2017 15:06:25 GMT

If I have the question right (and I'm not really sure about that), you are starting with A and looking to calculate B and C.

If that's the case, the formulas are straightforward (and now corrected):

B = int(A/100);

C = mod(A, 100);

Somehow I get the feeling that I'm leaving out a key piece of the question though. I can't draw the link between this table and your original table.

Re: Substr YYYY and MM from Sas date YYYYMM

Cruise — Thu, 23 Mar 2017 14:39:14 GMT

Hi Reeza,

I tried your first code with and without left.

/*Reeza1*/
data x1; set x;
year = substrn(left(dob), 1, 4);
month = substrn(left(dob), 5, 2);
run; 
/*Reeza2*/
data feb.gro_id1; set feb.gro_id;
Date_type =input( put(dob, 6.), yymmn6.);
Year=year(date_type);
Month = month(date_type);
run;

The corresponding results in viewtable are:

Reeza1:

Reeza2:

Re: Substr YYYY and MM from Sas date YYYYMM (edited)

Cruise — Thu, 23 Mar 2017 14:48:33 GMT

Hi Respected,

I am starting with below. Date is truncated for confidentiality concern. So I have this 6 digit SAS date.

I tried your code as:

year=substr(left(dob),1,4);
month=substr(left(dob),5,2);
B = int(year/100);
C = mod(year, 100);

That is resulted in:

Any alternatives?

Thanks

Re: Substr YYYY and MM from Sas date YYYYMM (edited)

Astounding — Thu, 23 Mar 2017 15:04:47 GMT

Just mixing and matching names ... should be:

year = int(dob/100);

month = mod(dob, 100);

Re: Substr YYYY and MM from Sas date YYYYMM (edited)

Cruise — Thu, 23 Mar 2017 15:11:15 GMT

I still have 1890, 1898 sort of years after this.

Re: Substr YYYY and MM from Sas date YYYYMM (edited)

Tom — Thu, 23 Mar 2017 15:17:52 GMT

If you are getting DOB in the 1800's then either you have some really old people or the process that created that field did something wrong.

Most likely it assumed the wrong century for a date string with a two digit year. If you cannot correct that then you will need to do the best you can to fix the DOB after the fact. If you have other information that could lead you believe that the 1890 is unreasonable but that 1990 is reasonable for that person then just add 100 years to the DOB.

Re: Substr YYYY and MM from Sas date YYYYMM (edited)

Reeza — Thu, 23 Mar 2017 15:47:31 GMT

@Cruise Look at the first picture you posted. It looks like 1890. If it's not supposed to be 1890 what is it supposed to be. According to what I see, the code looks like it ran correctly. Either

1. Your dates are in some other format than you've specified. Did you import this data from Excel, from a date field

2. Your dates are wrong

3. Dates are correct and you have some really old people.

Re: Substr YYYY and MM from Sas date YYYYMM (edited)

Cruise — Thu, 23 Mar 2017 16:33:17 GMT

Hi @Reeza and @Tom,

1. You're right. As a result of this experiment, I realized that I have old and young folks with a cancer hospitalized between 1995-2013 with age range of 7-107yrs. Code below shows how I tested and variable "test" has only 0,1 values depending whether they were hospitalized before or after their birthday.

2. Suggested formats didn't work but either substr and substrn identically worked out.

3. Critical thinking was required to question, perhaps folks I have here might just be old vs very young (7-107yrs).

Can I select two answers for SOLUTIONS? I truly appreciate your help indeed.

dy = substrn(left(dob), 1, 4);
dm = substrn(left(dob), 5, 2);
difference=hospitalized_year-dy;
cage=input(AGE,3.);
test=difference-cage;