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

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
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

2 REPLIES 2
ballardw
Super User

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.

Patrick
Opal | Level 21

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

 

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
  • 2 replies
  • 4315 views
  • 2 likes
  • 3 in conversation