topic Problem with data dates in SAS Programming

Problem with data dates

georgel — Fri, 13 May 2022 15:54:17 GMT

Dear SAS users and experts,

I would like to resolve the issue below for my datasets.

I have the right format for the data Issue_date and Offer_datec but

I have tried to calculate the diffeence with meaningless results . Please see my file attached

The code

data el.Data_test(keep=Issue_date Offer_date Offer_datec Dif_Issue_Offer);
    set Data_ipo4;
     * Dif_Issue_Offer=Listing_datec-Issue_date;
	   * Offer_datec=input(Offer_date,8.);
        *Offer_datec=Offer_datec-0;
        * format Offer_datec YYMMDDN8.;
	     Dif_Issue_Offer=Offer_datec-Issue_Date;
run;

Many thanks in advance,

George

Re: Problem with data dates

PaigeMiller — Fri, 13 May 2022 16:07:01 GMT

@georgel wrote:

Dear SAS users and experts,

I would like to resolve the issue below for my datasets.

I have the right format for the data Issue_date and Offer_datec but

Format is not the issue. Both unformatted variables must be number of days since 01JAN1960 in order for SAS to be able to treat them as dates. Your variable offer_datec is not on this scale. Thus any subtraction will yield gibberish.

data want;
    set el.Data_test;
    offer_daten=input(put(offer_datec,8.),yymmdd8.);
    Dif_Issue_Offer=Offer_daten-Issue_Date;
run;

Now Offer_dateN and Issue_Date are on the same scale, number of days since 01JAN1960, and the subtraction works.

Re: Problem with data dates

Reeza — Fri, 13 May 2022 16:10:08 GMT

offer_datec is a datetime, not date.

Use datepart() to convert it to a date variable.

@georgel wrote:

Dear SAS users and experts,

I would like to resolve the issue below for my datasets.

I have the right format for the data Issue_date and Offer_datec but

I have tried to calculate the diffeence with meaningless results . Please see my file attached

The code
data el.Data_test(keep=Issue_date Offer_date Offer_datec Dif_Issue_Offer);
    set Data_ipo4;
     * Dif_Issue_Offer=Listing_datec-Issue_date;
	   * Offer_datec=input(Offer_date,8.);
        *Offer_datec=Offer_datec-0;
        * format Offer_datec YYMMDDN8.;
	     Dif_Issue_Offer=Offer_datec-Issue_Date;
run;
Many thanks in advance,

George

Re: Problem with data dates

Tom — Fri, 13 May 2022 16:20:07 GMT

@georgel wrote:

Dear SAS users and experts,

I would like to resolve the issue below for my datasets.

I have the right format for the data Issue_date and Offer_datec but

NO. You have the WRONG format attached to one of those variables.

This is easy to see by looking at the actual VALUES that they contain.

proc means data="c:\downloads\data_test" min max mean;
  format _all_;
run;

Check those min/max values and see what date or datetime they represent.

data test;
  input date @@;
  put date=comma12. date :datetime19.  date date9. ;
cards;
 15000.00       20402.00
19880607.00    20200603.00
;

Results:

date=15,000 01JAN1960:04:10:00 25JAN2001
date=20,402 01JAN1960:05:40:02 10NOV2015
date=19,880,607 18AUG1960:02:23:27 *********
date=20,200,603 21AUG1960:19:16:43 *********

It looks like ISSUE_DATE has date values.

But OFFER_DATEC has neither DATE nor DATETIME values. It looks like it just has integers in the style YY,YYM,MDD.

So you need to convert the values stored in OFFER_DATEC into actual DATE values before you can use it as a date. Either in your calculations or to use with a display format that expects date values.

data el.Data_test ;
   set Data_ipo4(keep=ISSUE_DATE OFFER_DATEC);
   offer_datec = input(put(offer_datec,z8.),yymmdd8.);
   Dif_Issue_Offer=Offer_datec-Issue_Date;
   format issue_date offer_datec date9.;
run;

Re: Problem with data dates

georgel — Fri, 13 May 2022 16:23:41 GMT

Dear Miller,
Thanks a million for your quick respond.
It works perfectly

Re: Problem with data dates

Tom — Fri, 13 May 2022 16:23:51 GMT

Those values are not datetime values either. If they are they all from the year 1960.

@Reeza wrote:

offer_datec is a datetime, not date.

Use datepart() to convert it to a date variable.
@georgel wrote:

Dear SAS users and experts,

I would like to resolve the issue below for my datasets.

I have the right format for the data Issue_date and Offer_datec but

I have tried to calculate the diffeence with meaningless results . Please see my file attached

The code
data el.Data_test(keep=Issue_date Offer_date Offer_datec Dif_Issue_Offer);
    set Data_ipo4;
     * Dif_Issue_Offer=Listing_datec-Issue_date;
	   * Offer_datec=input(Offer_date,8.);
        *Offer_datec=Offer_datec-0;
        * format Offer_datec YYMMDDN8.;
	     Dif_Issue_Offer=Offer_datec-Issue_Date;
run;
Many thanks in advance,

George

Re: Problem with data dates

ballardw — Fri, 13 May 2022 16:34:16 GMT

Printing some observations from that data set shows this in the log:

220  proc print data=tmp1.data_test (obs=5);
221  run;

NOTE: There were 5 observations read from the data set TMP1.DATA_TEST.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


ERROR: There was a problem with the format so BEST. was used.
ERROR: There was a problem with the format so BEST. was used.
ERROR: There was a problem with the format so BEST. was used.
ERROR: There was a problem with the format so BEST. was used.
ERROR: There was a problem with the format so BEST. was used.
ERROR: There was a problem with the format so BEST. was used.
ERROR: There was a problem with the format so BEST. was used.
ERROR: There was a problem with the format so BEST. was used.
ERROR: There was a problem with the format so BEST. was used.
ERROR: There was a problem with the format so BEST. was used.
ERROR: There was a problem with the format so BEST. was used.
ERROR: There was a problem with the format so BEST. was used.

The latest date that SAS will use is 31DEC20000 (yes roughly 18,000 years from now) which has a numeric value (number of days) of 6,589,335. Which is considerably smaller than the values of your Order_datec such as 20,141,002.

If 20141002 is supposed to be 02OCT2014 then you need to convert to an actual date. Something like this:

data example;
  x=20121005;
  datefromx = input(put(x,8. -L),yymmdd10.);
  format datefromx date9.;
run;