topic Re: Difference between two different format dates. in SAS Programming

Difference between two different format dates.

Sujithpeta — Mon, 12 Nov 2018 19:51:10 GMT

Hello,

I've two variables DEATH_DT with format BEST12. and INDEX with format YYMMDDN8. I tried following to get dates difference.

data M.Death;
set M.Death;
format DEATH_Date YYMMDDN8.;
DEATH_Date = DEATH_DT;

death_days = (DEATH_Date-Index);
run;

Surprisingly the outcome of death_days is also date with format BEST12. I highly appreciate if someone could help me with this.

Thanks

Re: Difference between two different format dates.

Reeza — Mon, 12 Nov 2018 20:04:30 GMT

@Sujithpeta wrote:

Hello,

I've two variables DEATH_DT with format BEST12. and INDEX with format YYMMDDN8. I tried following to get dates difference.

A 'date' with a best12 format will not be a SAS date and you cannot do a subtraction and expect the correct value, assuming it looks like YYMMDD, ie 20180101 as a number. You will first need to convert it to a date and then use it. However the exact code depends on how the variable looks like (the informal to use) and you would need to provide that information Your code would end up looking like:

death_sas_date = input(put(death_dt, 8. -l), yymmdd10.);

death_days = death_sas_date - Index;

Re: Difference between two different format dates.

PaigeMiller — Mon, 12 Nov 2018 20:13:07 GMT

Can you show us the values on at least one observation of variables DEATH_DT and INDEX? Thank you.

Re: Difference between two different format dates.

PaigeMiller — Mon, 12 Nov 2018 20:14:00 GMT

@Reeza wrote:

@Sujithpeta wrote:

Hello,

I've two variables DEATH_DT with format BEST12. and INDEX with format YYMMDDN8. I tried following to get dates difference.

A 'date' with a best12 format will not be a SAS date

Sure it can be a SAS date. Formats don't change whether or not something is a SAS date.

Re: Difference between two different format dates.

Reeza — Mon, 12 Nov 2018 20:38:51 GMT

@PaigeMiller wrote:

@Reeza wrote:

@Sujithpeta wrote:

Hello,

I've two variables DEATH_DT with format BEST12. and INDEX with format YYMMDDN8. I tried following to get dates difference.

A 'date' with a best12 format will not be a SAS date

Sure it can be a SAS date. Formats don't change whether or not something is a SAS date.

A 'date' with a best12 format will not be a SAS date and you cannot do a subtraction and expect the correct value, assuming it looks like YYMMDD, ie 20180101 as a number.

I stand by that statement 🙂 If it's a number and looks like YYMMDD (20180101) with the BEST format it's 99% unlikely to be a SAS date.

If it was 21048 that's likely a SAS date.

Re: Difference between two different format dates.

Sujithpeta — Mon, 12 Nov 2018 20:45:02 GMT

First column is the DEATH_DT with format BEST12. Length 8 and Informat 12.

Second column is the INDEX with format YYMMDDN8. Length 8 and informat 8.

I don't know why the date is in a non-SAS format because it's the original datasets

Re: Difference between two different format dates.

Reeza — Mon, 12 Nov 2018 21:06:43 GMT

Given this, the code I posted earlier should work. If it does not, post your code and log.

@Sujithpeta wrote:

First column is the DEATH_DT with format BEST12. Length 8 and Informat 12.

Second column is the INDEX with format YYMMDDN8. Length 8 and informat 8.

I don't know why the date is in a non-SAS format because it's the original datasets

Re: Difference between two different format dates.

Sujithpeta — Mon, 12 Nov 2018 22:55:39 GMT

@Reeza I tried the code but it's throwing error.

I also tried changing the YYMMDDN10. to YYMMDDN8. I had the same error.

Re: Difference between two different format dates.

Kurt_Bremser — Tue, 13 Nov 2018 04:17:38 GMT

Read @Reeza's post thoroughly, with regards to the informat. There is no yymmddn informat, it only exists as format. Use the yymmdd informat.

Re: Difference between two different format dates.

PaigeMiller — Tue, 13 Nov 2018 11:50:52 GMT

@Reeza wrote:

@PaigeMiller wrote:

@Reeza wrote:

@Sujithpeta wrote:

Hello,

I've two variables DEATH_DT with format BEST12. and INDEX with format YYMMDDN8. I tried following to get dates difference.

A 'date' with a best12 format will not be a SAS date

Sure it can be a SAS date. Formats don't change whether or not something is a SAS date.

A 'date' with a best12 format will not be a SAS date and you cannot do a subtraction and expect the correct value, assuming it looks like YYMMDD, ie 20180101 as a number.

I stand by that statement 🙂 If it's a number and looks like YYMMDD (20180101) with the BEST format it's 99% unlikely to be a SAS date.

If it was 21048 that's likely a SAS date.

Up until a later post, there was no such statement by the original poster that "it looks like YYMMDD", and so this (at the time) is an unsupported assumption.

Re: Difference between two different format dates.

PaigeMiller — Tue, 13 Nov 2018 11:51:28 GMT

@Sujithpeta wrote:

@Reeza I tried the code but it's throwing error.

I also tried changing the YYMMDDN10. to YYMMDDN8. I had the same error.

Please copy and paste the SASLOG as text (not as an image) into the {i} window.

Re: Difference between two different format dates.

Sujithpeta — Tue, 13 Nov 2018 14:51:59 GMT

My bad @Kurt_Bremser, I corrected the code as @Reeza posted

data M.Death;
set M.Death;
  Death_SAS_Date = input(put(DEATH_DT, 8. -l), YYMMDD10.) - Index;
  Death_days = Death_SAS_Date - Index;
run;

Death_SAS_Date still in the format of BEST12. and Death_days are also in the format of BEST12.

Re: Difference between two different format dates.

Kurt_Bremser — Tue, 13 Nov 2018 16:45:27 GMT

Post the dataset in a data step with datalines.

Until we can know your data (see Maxim 3), further guessing around is useless.

Re: Difference between two different format dates.

Sujithpeta — Tue, 13 Nov 2018 18:33:22 GMT

data WORK.DEATH;
  infile datalines dsd truncover;
  input DEATH_DT:32. Index:YYMMDDN8.;
  format Index YYMMDDN8.;
  label Index="Claim Admission Date";
datalines;
. 20120910
. 20120801
. 20120927
20140520 20120809
. 20121019
. 20120103
. 20120731
. 20120308
20150121 20120109
. 20121005
;;;;

Does this help @Kurt_Bremser?

Re: Difference between two different format dates.

Reeza — Tue, 13 Nov 2018 19:07:32 GMT

  Death_SAS_Date = input(put(DEATH_DT, 8. -l), YYMMDD10.) - Index;
  Death_days = Death_SAS_Date - Index;

You're subtracting the index twice. You can literally copy and paste my previous code to get this right.

@Sujithpeta wrote:

My bad @Kurt_Bremser, I corrected the code as @Reeza posted
data M.Death;
set M.Death;
  Death_SAS_Date = input(put(DEATH_DT, 8. -l), YYMMDD10.) - Index;
  Death_days = Death_SAS_Date - Index;
run;
Death_SAS_Date still in the format of BEST12. and Death_days are also in the format of BEST12.

Re: Difference between two different format dates.

Sujithpeta — Tue, 13 Nov 2018 20:36:31 GMT

I corrected but it still gives the same output @Reeza

Re: Difference between two different format dates.

Reeza — Tue, 13 Nov 2018 22:24:18 GMT

When I did it with the correct formulas it worked perfectly.

When you say something isn’t working, show your code. I suspect you still have two indexes in your code. The original code, without the N in the format and with only one index is still correct. If that doesn’t work post your full code and log.

Re: Difference between two different format dates.

Kurt_Bremser — Wed, 14 Nov 2018 07:14:02 GMT

@Sujithpeta wrote:

data WORK.DEATH;
  infile datalines dsd truncover;
  input DEATH_DT:32. Index:YYMMDDN8.;
  format Index YYMMDDN8.;
  label Index="Claim Admission Date";
datalines;
. 20120910
. 20120801
. 20120927
20140520 20120809
. 20121019
. 20120103
. 20120731
. 20120308
20150121 20120109
. 20121005
;;;;

Does this help @Kurt_Bremser?

Testing code before posting it here is not a crime, so please do it. You once again committed the mistake of using a non-existing informat that I already advised you about.

After fixing the data step, it can be clearly proven that @Reeza's code as initially posted works:

data WORK.DEATH;
input DEATH_DT :32. Index :YYMMDD8.;
format Index YYMMDDN8.;
label Index="Claim Admission Date";
datalines4;
. 20120910
. 20120801
. 20120927
20140520 20120809
. 20121019
. 20120103
. 20120731
. 20120308
20150121 20120109
. 20121005
;;;;
run;

data Death;
set Death;
format death_sas_date yymmddn8.;
death_sas_date = input(put(death_dt, 8. -l), yymmdd10.);
death_days = death_sas_date - Index;
run;

proc print data=death noobs;
run;

Result:

                         death_     death_
DEATH_DT       Index    sas_date     days

       .    20120910           .        . 
       .    20120801           .        . 
       .    20120927           .        . 
20140520    20120809    20140520      649 
       .    20121019           .        . 
       .    20120103           .        . 
       .    20120731           .        . 
       .    20120308           .        . 
20150121    20120109    20150121     1108 
       .    20121005           .        .

Re: Difference between two different format dates.

Sujithpeta — Wed, 14 Nov 2018 14:16:06 GMT

@Reeza @Kurt_Bremser thanks guys, I apologize for not properly following the instructions.