Change BEST. format to date format

[LucyDang]

Hello SAS gurus,

I have read quite a lot about BESTw. format to find the answer before posting this topic. However, my case seems to be different. I imported an excel file to SAS data file with proc import. I used proc content and realized that "Date" variable is Numeric with "BEST." format. It seems to me that it's supposed to be "BEST12.". But, no, SAS just writes "BEST." format.

I  wrote a code to change the format to the new variable named DateSAS with date9. format, although SAS assigned date9. format to DataSAS but there is no value recorded for this variable in the data table. 

Would you please show me another way to correct my code so that I can change the numeric BEST. format to data format? Thank you so much!

data stock.performanceclean;
set stock.PerformM;
where Returns is not missing and Returns ne "B";
DateSAS=input(input(Date,best.),date9.);
ReturnsF=input(Returns,22.);
keep ticker Company_Name EventDate Date DateSAS Returns ReturnsF MrkReturn;
run;

proc contents data=stock.performanceclean;
run;

The 1st picture is proc contents result.

The second picture is the picture of the table, there is no value of DateSAS variable.

[Kurt_Bremser]

Please do not post data in pictures. Post data as text in an appropriate window, and use data steps with datalines to present examples for SAS datasets. We can then reliably(!) recreate your datasets with a simple copy/paste and submit. Help us to help you.

Read the log. It will alert you to this:

DateSAS=input(input(Date,best.),date9.);

The input() function expects a character argument and returns a numeric result if a numeric informat is used (which is the case here), so you get a NOTE for an automatic type conversion (always bad).

Try this instead:

DateSAS = input(put(Date,8.),yymmdd8.);
format DateSAS yymmdd10.;

eg

data have;
input date;
cards;
20070103
;
run;

data want;
set have;
DateSAS = input(put(Date,8.),yymmdd8.);
format DateSAS yymmdd10.;
run;

proc print data=want noobs;
run;

Result:

  date         DateSAS

20070103    2007-01-03

[RW9 · Accepted solution]

Unfortunately your problem is common, and comes from combining a garbage data format - Excel, with a guessing procedure - proc import.  Forget post manipulation, sort out getting data into SAS.  Do this by saving your Excel file to CSV - CSV is a plain text file with data items separated by commas.  It is an ideal plain file format for data transfer.  

Next run a proc import over it which will output the actual code used to the log.  That code you can then copy into your program in replace of the proc import and then you have full control over how the data is read in.  Changing informats, lengths etc.  So your import might then look like:

data want;
  infile ".../yourdata.csv" dlm=",";
  length var1 $200
         var2 8;
  informat var2 yymmdd10.;
  format var2 date9.;
  input var1 $ 
        var2;
run;

Your data would then look like the parameters you put in the length/format/informat and label if need be.

Robust data transfer never includes Excel or proc import!

[LucyDang]

That's amazing! Thank you so much!! Your recommendation is excellent!! Now I understand the reason and also know how to deal with excel format and sas format!  Next time, I do not need to worry about excel --> SAS ^__^

[LucyDang]

Dear RW9,

This is the line of my data. Var1 is PERMNO, Var2 is Date, Var3 is Ticker, and so on. When N=5040, SAS read the first and second value for PERMNO and Date. Ticker is supposed to be 24702R10, but SAS skipped 24702R10 and replace with DELL. Why did SAS skip 24702R10? I set the format of Ticker is Ticker $10.

[Kurt_Bremser]

Please do NOT post data, logs or code in pictures. Use the proper buttons for this ({i} for data and logs, little running man for code).

You have tried to read non-numerical data into column cusip. For detailed help, post code and data as mentioned above.