how to change date format

[sasphd]

hello 

I import sas file usinh this line 

proc import datafile = 'C:\MaRecherche\IPO\data_full_sample\FF_factors.xlsx' DBMS = Excel OUT = Factors ; sheet="F-F_Research_Data_Factors";

when I get the data the variable date has a format best12. et informat 12. I want to change the format to YYMMDDN8. and informat 8. 

I do it in this way 

data JOINT;
set JOINT;
format date yymmddn8.;
informat date 8.;
run;

it does not work. it gives strange dates!!!!

[Kurt_Bremser]

Changing an informat has no effect. The informat is only used when a variable is read from an external file with an INPUT statement.

Which values do you see without a format?

[sasphd]

i see this 06AUG2487 for the date 192701!!!!!!!

[Kurt_Bremser]

A raw value of 192701 is, as you see, very far in the future, both in SAS and Excel.

(in Excel it would be in 2427)

What do you see in that cell when you open the spreadsheet in Excel?

[sasphd]

192701 is 1927jan (year/month)

when I open excel I see 192701 and is a standard format in excel 

[Kurt_Bremser · Accepted solution]

So someone tried to be cute and fumbled the ball.

Do this:

date = input(put(date,z6.)!!'01',yymmdd8.);
format date yymmn6.;

immediately after the import.

[Kurt_Bremser]

Or, even shorter:

date = input(put(date,z6.),yymmn6.);
format date yymmn6.;

[ballardw]

---

@sasphd wrote:

i see this 06AUG2487 for the date 192701!!!!!!!

---

And what date should 192701 be?

SAS stores dates and the number of days from 1Jan1960. Which is why applying a date format to a random number, which a BEST informat indicates the value was read a simple number, generates values that appear to be "incorrect".

You will likely need to do some manipulation of your existing values to get to a numeric value that represents the appropriate date in SAS terms. To do that we need to know what values you actually have and what dates they should be, at least for a few samples.

One suspects the spreadsheet did not contain a date either. Instead someone was entering just a Year and Month number, not an actual date in any term (need a day of month to be an actual date).