topic Re: how to change date format in SAS Programming

how to change date format

sasphd — Fri, 23 Oct 2020 19:05:40 GMT

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!!!!

Re: how to change date format

Kurt_Bremser — Fri, 23 Oct 2020 19:10:37 GMT

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?

Re: how to change date format

sasphd — Fri, 23 Oct 2020 19:12:50 GMT

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

Re: how to change date format

Kurt_Bremser — Fri, 23 Oct 2020 19:28:25 GMT

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?

Re: how to change date format

ballardw — Fri, 23 Oct 2020 19:29:39 GMT

@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).

Re: how to change date format

sasphd — Fri, 23 Oct 2020 19:54:11 GMT

192701 is 1927jan (year/month)

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

Re: how to change date format

Kurt_Bremser — Fri, 23 Oct 2020 20:06:05 GMT

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.

Re: how to change date format

Kurt_Bremser — Sat, 24 Oct 2020 11:06:28 GMT

Or, even shorter:

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