BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jessicarocha1
Calcite | Level 5

 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?

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

 

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

6 REPLIES 6
ballardw
Super User

 

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;

jessicarocha1
Calcite | Level 5

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.

ballardw
Super User

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

 

Tom
Super User Tom
Super User

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
jessicarocha1
Calcite | Level 5
Thank you!!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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