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?
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;
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;
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.
@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.
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
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: