Desktop productivity for business analysts and programmers

Question format date

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Question format date

 Hi! I am importing a text file into SAS that has a date field in the form "dd / mm / yyyy hh: mm: ss", when I run my query with the filters, SAS returns the date field in format MMDDYYYY, the problem is that he is reversing and putting my day as month, when the date is 12/05/2018 he returns me as 05/12/2018. Can you help me?


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 13,338

Re: Question format date

Posted in reply to jessicarocha1

 

How are you importing the data file? Are you using Proc import or writing a data step to read the file?

If you used Proc Import and the file was actually text then a data step to read the data was generated and would have an INFORMAT statement indicating which format was used to read the data. You might post that, or the entire data step generated to show us exactly what you have.

 

Also what is your setting for DATESTYLE? You can run this code (hopefully) to find out:

proc options option=datestyle;run;

 

This option sets some language/nationality defaults for date related values. If the result in the log shows

 DATESTYLE=MDY

then the default is to treat values as month day year not your day month year.

 

You may need to either specify a different informat and format for date variables when reading them or if you intend to rely on Proc Import set the system option to what you seem to need:

 

Options datestyle=DMY;

View solution in original post


All Replies
Solution
3 weeks ago
Super User
Posts: 13,338

Re: Question format date

Posted in reply to jessicarocha1

 

How are you importing the data file? Are you using Proc import or writing a data step to read the file?

If you used Proc Import and the file was actually text then a data step to read the data was generated and would have an INFORMAT statement indicating which format was used to read the data. You might post that, or the entire data step generated to show us exactly what you have.

 

Also what is your setting for DATESTYLE? You can run this code (hopefully) to find out:

proc options option=datestyle;run;

 

This option sets some language/nationality defaults for date related values. If the result in the log shows

 DATESTYLE=MDY

then the default is to treat values as month day year not your day month year.

 

You may need to either specify a different informat and format for date variables when reading them or if you intend to rely on Proc Import set the system option to what you seem to need:

 

Options datestyle=DMY;

New Contributor
Posts: 3

Re: Question format date

Hello, I imported the text file with the "DT_VEN" field in the format DD / MM / YYYY HH: MM: SS to the SAS and it automatically entered the type of each field, I did not change the formats. When I ran the query the format of the date changed.

 

A part of the query:

proc sql;

datepart(dt_venc) ddmmyy10. as VENCIM;

 

I tried changing the format of the field when importing the file, but I did not succeed.

 

 

 

Sorry for the errors in writing, I'm Brazilian.

Super User
Posts: 13,338

Re: Question format date

Posted in reply to jessicarocha1

@jessicarocha1 wrote:

Hello, I imported the text file with the "DT_VEN" field in the format DD / MM / YYYY HH: MM: SS to the SAS and it automatically entered the type of each field, I did not change the formats. When I ran the query the format of the date changed.

 

A part of the query:

proc sql;

datepart(dt_venc) ddmmyy10. as VENCIM;

 

I tried changing the format of the field when importing the file, but I did not succeed.

  

Sorry for the errors in writing, I'm Brazilian.


1) Go back to the Proc Import step

2) use the option guessingrows=max  (without this SAS only examines a few records and may make poor guesses as to what is in each column of your source file)

2) Proc Import will generate data step code in the Log.

3) Copy that portion of the log (You may want to save it as well)

4) Paste that portion of the log to a code box opened with the forum {I} menu icon.

 

That will tell us exactly what format and informat SAS used for the data.

It may be that you will want to change the Informat and the format. You can do that by using that same code from the log as program to read the data.

 

Super User
Super User
Posts: 7,936

Re: Question format date

Posted in reply to jessicarocha1

If you are reading a text file you will have more control if you write your own data step instead of using PROC IMPORT. That way you can TELL SAS what format to use.

 

One problem is that your data is NOT in a format that can be understood without making some assumption about whether the month of the year or the day of the month is listed first.  If you format your dates using either YMD order or using SAS's DATE format style then you can avoid this problem in the future.  

 

If you use one of the ANYDT.... series of informats then it will use the setting of the DATESTYLE option to understand how to interpret ambiguous strings like 12/05.

 

%put %sysfunc(getoption(datestyle,keyword));
data test ;
  input 
    @1 date1 anydtdte32. 
    @1 date2 :mmddyy.
    @1 date3 :ddmmyy.
  ;
  format date1-date3 yymmdd10. ;
  put (_all_) (=);
cards;
12/05/2016 10:31:00
;
DATESTYLE=MDY
date1=2016-12-05 date2=2016-12-05 date3=2016-05-12
Super User
Posts: 9,919

Re: Question format date

Posted in reply to jessicarocha1

All these questions would of course be moot if people adhered to standards: https://en.wikipedia.org/wiki/ISO_8601

After all, that one's been around for thirty years now.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 3

Re: Question format date

Posted in reply to jessicarocha1
Thank you!!!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 193 views
  • 1 like
  • 4 in conversation