Issue with dates using import wizard code

[FC_Chris]

Hello All,

I'm using SAS 9.4, and have a csv file that I was successfully able to import using the import wizard. However, when I attempt to execute the import using the code generated in the log, I am getting invalid data errors for several of the date fields. These were formatted as mmddyy10., which matches format of date fields in my file (ie. 3/24/2008). How is it that the same code that was generated from the wizard does not work when run in a program? I tried it again using date9. but am getting the same errors.

From an automation perspecitve, I'd much rather have use the code than having to run the wizard every time. Any assistance on this is much appreciated, as I've tried to solve this with no luck. Let me know if there's any additional info that would be helpful.

Thanks!

Chris

[Cynthia_sas]

Hi:

  Consider this comma separated data that represents some basic family information about 2 people:
Joseph,23feb1934,06/15/1953,31may1957,8/31/1961,Mary
Dorothy,01mar1935,06/15/1954,1958/05/29,28/6/1960,Nicholas

And, note how the first date is in the form ddmonyyyy, and the second date is in mmddyy form; but the 3rd and 4th dates are displayed differently. Let's say I have this data in a file called c:\temp\gen_data.csv, then the program to read this data and control how to read the dates would be as shown below.

Note that my program reads the BDAY variable using the DATE. informat; then GRAD_HS is read using the MMDDYY. informat; but the GRAD_COLLEGE and the MARRY variables are read with the ANYDTDTE informat because each record uses a different way to represent the date and so, if I want to give SAS the best chance of reading the date value, I need to use a more flexible informat. But, no matter what INFORMAT I use to READ the data from the CSV file into a SAS dataset, I can use a pre-defined SAS format for display purposes. Report #1 shows ALL the dates formatted with MMDDYY10. format and Reprot #@ shows using the WORDDATE. format -- no matter how I needed to read them, I can display them anyway I want.

cynthia

data readdate;

  infile 'c:\temp\gen_data.csv' dlm=',' dsd;

  input name $ bday : date. grad_HS : mmddyy. grad_college : anydtdte. marry : anydtdte. spouse $;

  format bday grad_HS grad_college marry mmddyy10.;

run;

proc print data=readdate;
title '1) Note how ANYDTDTE reads ANY date form as input';
title2 'But date. and mmddyy. can ONLY read dates in one specific form';
title3 'Then the date can be displayed any way you want';
run;

proc print data=readdate;
title '2) dates formatted with worddate.';
format bday grad_HS grad_college marry worddate.;
run;

Produces this output:

[ballardw]

Take a close look at those errors if they are only occurring for some records. You should get a dump of the current input line and the values of variables read. Examine that input line for INVALID dates such as 30 Feburary, 2/30/yyyy, or 29 Feb in a non-leapyear or 31 days in any month that only has 30. Or perchance extreme values for a year such as 20013.

You may also have some data sent to you as dd/mm/yyyy.

Post some of the log showing the error.