topic importing CSV file with different datetime format in SAS Programming

importing CSV file with different datetime format

devsas — Fri, 13 Oct 2017 20:53:00 GMT

Ok, I thought I would solve this easily, but after trying various methods im still not getting it. I use the following code to import csv file which has 6 date fields in original date time format stored as character - Some of the values are missing and denoted by 'NULL'. The date values look like this

2017-10-31 00:00:00.000

DATA chpModel;
    INFILE "C:\Desktop\CHP model results\CHP model results 12 Oct.csv" 
    DELIMITER = "," 
    MISSOVER DSD 
    FIRSTOBS=2 
    LRECL=32767;
Length acctno $50	subsno $50	phaseid $50	AcctnoActiveMonth $50 PhaseActiveMonth $50	PhaseStart $50	Phaseend $50	LastmonthBenpkg $20	TotalPremium	Totalpayment	Balance	AR	DeferredRevenue	Grace_period_Term $50	Financial_term $50
;
    INPUT  acctno $	subsno $	phaseid $	AcctnoActiveMonth  $	PhaseActiveMonth $	PhaseStart $	Phaseend $	LastmonthBenpkg $	TotalPremium	Totalpayment	Balance	AR	DeferredRevenue	Grace_period_Term $	Financial_term $
;
format AcctnoActiveMonth mmddyy10 PhaseActiveMonth mmddyy10	PhaseStart mmddyy10	Phaseend mmddyy10 ;
RUN;

The file is imported fine but the formatting stays the same as original. I also tried importing dates as numeric, but the same result.

I know one of the things I can do is converting to date from character once the import is done. But for one, it will change the sequence of the fields and second, with the formatting I have, I was having problem even doing that. I want the final dates to look like

10/31/2017

Re: importing CSV file with different datetime format

ballardw — Fri, 13 Oct 2017 21:10:18 GMT

1) Do not import as character

2) Use an informat to read the data directly into a SAS Date or datetime variable.

Maybe something like this BEFORE the INPUT statement:

informat AcctnoActiveMonth PhaseActiveMonth PhaseStart Phaseend anydtdtm. ;

3) If you do not want the time portion then you may have to read as datetime and then convert to date.

AcctnoActiveMonth = datepart(AcctnoActiveMonth ); repeat for each variable.

4) correctly assign the date format wanted:

format AcctnoActiveMonth mmddyy10. PhaseActiveMonth mmddyy10. PhaseStart mmddyy10. Phaseend mmddyy10. ;

Actually you only need the format once at the end of list of variables that use the same format. The . is part of the format named and you did not show any of the error messages your code generated because of this coding error. The periods are needed because mmddyy10 is a valid SAS variable name.

Date, time and Datetime formats only apply to numeric values. When you read as text as in your example you would need to create a new variable to hold the numeric value and apply the format to.

This will generate some "invalid data" messages for the null values, but since they aren't dates and the variables will have a missing value that's basically what you want.

Re: importing CSV file with different datetime format

Patrick — Sun, 15 Oct 2017 09:47:07 GMT

@devsas

Would something like below do?

options datestyle=dmy;
data demo;
  infile datalines truncover dlm=',' dsd;
  input dttm ??:anydtdtm.;
  format dttm datetime23.3;
  datalines;
01jan2017 10:15:20
.
 
NULL
02jan2017 20:25:35
2017-10-31 00:00:00.000
;
run;

Or if you don't want to use the ?? argument so that you still get warnings for unexpected data:

proc format;
  invalue inDTTM (default=23)
    'NULL' =.
    other=[anydtdtm.]
  ;
run;

options datestyle=dmy;
data demo;
  infile datalines truncover dlm=',' dsd;
  input dttm :inDTTM.;
  format dttm datetime23.3;
  datalines;
01jan2017 10:15:20
.
 
NULL
02jan2017 20:25:35
2017-10-31 00:00:00.000
;
run;