Date fromatting in SAS

[markstringer]

I am attempting to run the following line of code to format a date for me as an integer instead: Date1=SUBSTR(End_Date,1,8)*1

This changes the date '2014-10-04' to '2' when really I want it to be an integer that looks like this '20141004'. 

[Ksharp]

There is a built-in format YYMMDDN. in sas to get it.

data _null_;
End_Date='2014-10-04' ;
Date1=input(End_Date,yymmdd10.);
format Date1 yymmddn8.;

put _all_;
run;

1    data _null_;
2    End_Date='2014-10-04' ;
3    Date1=input(End_Date,yymmdd10.);
4    format Date1 yymmddn8.;
5
6    put _all_;
7    run;

End_Date=2014-10-04 Date1=20141004 _ERROR_=0 _N_=1
NOTE: “DATA 语句”所用时间（总处理时间）:
      实际时间          0.40 秒
      CPU 时间          0.01 秒

[Kathryn_SAS]

The internal value of date1 is a SAS date. If you want an integer in the form yyyymmdd, then you can use the following:

8    data test;
9    End_Date='2014-10-04' ;
10   Date1=input(End_Date,yymmdd10.);
11   *format Date1 yymmddn8.;
12   daten=input(compress(end_date,'-'),8.);
13   put _all_;
14   run;

End_Date=2014-10-04 Date1=20000 daten=20141004 _ERROR_=0 _N_=1

[PaigeMiller]

So @Ksharp has already given you the answer, however, I would like to point out an important principle. Best practice is to handle dates using date functions and date formats and date informats, if at all possible. And to do this, you need to know if the value of End_Date (which you claim is '2014-10-04') is really character, or really numeric. If, as Ksharp guessed, the variable End_Date is character (is it?) then you need to use an informat (which in Ksharp's code is yymmdd10.). If End_Date was numeric, then you could use a format to convert it to the desired appearance.

[Tom]

If END_DATE is actually a DATE value then the reason you get 2 is because the actual number that SAS uses to represent the 4th day in October of 2014 is 20,000.  Since you asked SAS to convert that into a string so it could be used as the argument to the SUBSTR() function it used the BEST12. format so the result was 7 spaces followed by the 5 digit string 20000.  You then picked only the 7 spaces and the '2'.  And then forced SAS to convert that string into the number 2 which you multiplied by 1.

If END_DATE is instead a CHARACTER variabel it would also need to have those some 7 leading spaces for the result to be 2.

Example:

 71         data _null_;
 72           end_date= '04OCT2014'd;
 73           Date1=SUBSTR(End_Date,1,8)*1;
 74           put (_all_) (=/);
 75         run;
 
 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       73:16   
 NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
       73:9   
 end_date=20000
 Date1=2
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 76         
 77         data _null_;
 78           end_date= '       2014-10-04';
 79           Date1=SUBSTR(End_Date,1,8)*1;
 80           put (_all_) (=/);
 81         run;
 
 NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
       79:9   
 end_date=2014-10-04
 Date1=2
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds

If you want to convert a STRING like '2014-10-04' into a DATE then using the INPUT() function and the YYMMDD informat.  Make sure tor remove the leading spaces.

actual_date = input(left(end_date),yymmdd10.);

If you want the value of ACTUAL_DATE to print in the style YYYYMMDD then attach the YYMMDDN format to it.

format actual_date yymmddn8.;

If you want to make the variable DATE1 be a NUMBER in the style YY,YYM,MDD then use the PUT() function with YYMMDDN8. format to convert the date into a string so you can then use the INPUT() function with the normal numeric informat of 8. to convert that string back into a number.

date1 = input(put(actual_date,yymmddn8.),8.);

[Quentin]

As a side note to Tom's very thorough explanation (thanks!), many people think the NOTE in the log about automatic type conversion should be treated as an error, to avoid problems like this.  Many log scanners are coded to treat this NOTE as not allowed to appear in a log.

There is an undocumented system option which will allow you to turn this problematic NOTE (and others) into an actual error:

1    options dsoptions=note2err ;
2    data _null_;
3      end_date= '04OCT2014'd;
4      Date1=SUBSTR(End_Date,1,8)*1;
ERROR: Numeric value found where character value needed at line 4 column 16.
ERROR: Character value found where numeric value needed at line 4 column 9.
5      put (_all_) (=/);
6    run;

NOTE: The SAS System stopped processing this step because of errors.

[Kurt_Bremser]

For which purpose do you need that number? It cannot be used in further analysis, and if you need to export data in this format, the YYMMDDN format can handle this without conversion.