BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
plf515
Lapis Lazuli | Level 10

Hi all

 

Using SAS 9.4 on Windows.

 

I have also contacted SAS tech support with this issue.  But I wanted to see if anyone here knew a solution and also make people aware of what could be a real GOTCHA.

 

I have dates like this in a CSV file

 

Jan-18

 

Sep-17

Dec-17

 

Where Jan-18 represents January 2018 with the date of the month not recorded. 

 

 

But when I use PROC IMPORT

 

PROC IMPORT OUT= WORK.ruthie

            DATAFILE= "C:\personal\Consults\Ruthie Igwe\ruthie.csv"

            DBMS=CSV REPLACE;

     GETNAMES=YES;

     DATAROW=2;

RUN;

 

It gives me dates like this:

 

01JAN18:00:00:00 

01SEP17:00:00:00  01DEC17:00:00:00

 

With no notes, errors or warnings in the log.

 

Regards

 

Peter

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

@plf515: Obviously, a poor choice by PROC IMPORT when reading dates like 09/2017. Why they would choose anydtdtm40., rather than anydtdte7., is beyond me!

 

However, I'd just copy, paste and modify the code that PROC IMPORT shows in the log. Thus, for your data,:

data WORK.RUTHIE    ;
  infile '/folders/myfolders/ruthie.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
  informat Agecat $15. ;
  informat GPA best32. ;
  informat Any_dependents $3. ;
  informat Number_dependents best32. ;
  informat Married $3. ;
  informat Employed $12. ;
  informat HoursWork best32. ;
  informat Enroll $22. ;
  informat Start anydtdte7. ;
  informat End anydtdte7. ;
  format Agecat $15. ;
  format GPA best12. ;
  format Any_dependents $3. ;
  format Number_dependents best12. ;
  format Married $3. ;
  format Employed $12. ;
  format HoursWork best12. ;
  format Enroll $22. ;
  format Start date9. ;
  format End date9. ;
  input Agecat  $ GPA Any_dependents  $ Number_dependents
        Married  $ Employed  $ HoursWork Enroll  $
        Start End;
 run;

Art, CEO, AnalystFinder.com

 

View solution in original post

10 REPLIES 10
art297
Opal | Level 21

What does Sep-17 represent? Is is 01SEP2017, 17SEP2017, or something else. If the dates don't include the year, are they all from the same year and, if so, which year?

 

Art, CEO, AnalystFinder.com

 

plf515
Lapis Lazuli | Level 10

Hi Art

 

I edited my post.  Jan-18 represents January of 2018, with date of month not specified.

 

Peter

Reeza
Super User

Is this from the text file or when you've opened it with Excel?


Can you include a small screenshot of the dates in the CSV file from a text editor? Excel will interpret data on its own so the CSV may have something thats causing SAS to read it as a datetime. 

Also, what does the code indicate for the informat/format for the variable. 

 

What happens if you set GUESSINGROWS=MAX and re-run it?

plf515
Lapis Lazuli | Level 10

Hi Reeza

 

If I open the file in Notepad the dates look like this:

 

01/2018,

09/2017,12/2017

 

and these should represent an January of 2018, September of 2017 and so on.  The date of the month is unknown.

 

Thanks


Peter

art297
Opal | Level 21

@plf515: Obviously, a poor choice by PROC IMPORT when reading dates like 09/2017. Why they would choose anydtdtm40., rather than anydtdte7., is beyond me!

 

However, I'd just copy, paste and modify the code that PROC IMPORT shows in the log. Thus, for your data,:

data WORK.RUTHIE    ;
  infile '/folders/myfolders/ruthie.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
  informat Agecat $15. ;
  informat GPA best32. ;
  informat Any_dependents $3. ;
  informat Number_dependents best32. ;
  informat Married $3. ;
  informat Employed $12. ;
  informat HoursWork best32. ;
  informat Enroll $22. ;
  informat Start anydtdte7. ;
  informat End anydtdte7. ;
  format Agecat $15. ;
  format GPA best12. ;
  format Any_dependents $3. ;
  format Number_dependents best12. ;
  format Married $3. ;
  format Employed $12. ;
  format HoursWork best12. ;
  format Enroll $22. ;
  format Start date9. ;
  format End date9. ;
  input Agecat  $ GPA Any_dependents  $ Number_dependents
        Married  $ Employed  $ HoursWork Enroll  $
        Start End;
 run;

Art, CEO, AnalystFinder.com

 

plf515
Lapis Lazuli | Level 10

Thanks!  That worked.  But what a potential gotcha!

 

Peter

Reeza
Super User

@plf515 personally, I wouldn't consider this a gotcha. It read the dates correctly, but yes, you would need to use DATEPART to extract the date. Anytime you use a guessing procedure to import data, the onus should be on the user to verify the data. You'll run into the same issues with any tool (R/SAS/Python) when not specifying types explicitly. 

 

 

plf515
Lapis Lazuli | Level 10

But it read something with 7 characters as if it had 20.  That's a problem.

 

I think there should be at least a note that things are unclear.

 

art297
Opal | Level 21

@plf515: I agree!

 

@Reeza: I'd consider it a gotcha .. at least deserving of mention in the documentation! The developers, me thinks, should at least have looked for a colon before having the proc consider a date to be a datetime.

 

However, since correcting that now would mess up any historical code that folks have written I, for one, would like to know which specific entries will be considered to be datetimes rather than dates.

 

Art, CEO, AnalystFinder.com

ballardw
Super User

I wonder if the choice to use ANYDTDTM instead of ANYDTDTE for that data format was something marginally to reduce the "guessing" code of the Proc Import algorithm. As in "if it is a bit goofy try to treat as datetime" as the dates do work and when you get time parts as well.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 10 replies
  • 2966 views
  • 7 likes
  • 4 in conversation