How to infile time values from csv to sas table

[eunbi]

Hi all,

I am currently trying to create table from csv file. In the file, there is two time format column. 

For this columns, i tried to create table with time format with below codes.

data carinsurance_train;
infile "/home/eunvi920/assignment/carInsurance_train.csv" dlm=',' firstobs=2;
input Id Age Job $ Marital $ Education $ Default Balance HHInsurance CarLoan
 Communication $ LastContactDay LastContactMonth $ NoOfContacts 
 DaysPassed PrevAttempts Outcome $ CallStart time11. CallEnd time11. CarInsurance $;
 format CallStart time8. CallEnd time8.;
 put callstart=time8. callend=time8.;
run;

Table had created well but CallStart and CallEnd values are displayed as "."(dot).

CSV can see the time exactly now. 

example csv / sas result

How can i key in time values from csv to sas well? If my code has problem, please kindly let me know.

Thank you 

[novinosrin]

data carinsurance_train;
infile "/home/eunvi920/assignment/carInsurance_train.csv" dlm=',' firstobs=2;
input Id Age Job $ Marital $ Education $ Default Balance HHInsurance CarLoan
 Communication $ LastContactDay LastContactMonth $ NoOfContacts 
 DaysPassed PrevAttempts Outcome $ CallStart : time8. CallEnd : time8. CarInsurance $;
 format CallStart time8. CallEnd time8.;
 put callstart=time8. callend=time8.;
run;

I think you need a colon before your informat to make it a modified list input as edited above

[eunbi]

I tried your code but the result still dot(.) on the table.

By the way Thank you for concern my question! 🙂

[novinosrin]

Is it possible to share a a few records of your csv file, so we can test

[ballardw]

Please post some examples from your CSV as text in a code box opened using the forum's {I} icon. Copy from the file using Notepad, Wordpad or a similar text editor, not a spreadsheet program. The Spreadsheet may be applying display formatting that is not actually in the CSV file. Probably only need 2 or 3 lines that have the time values present.

If there are any sensitive values for the other variables use the editor to XXX over the values.

[eunbi]

Hi sir !

Thank you for the reply 1st!.

Below is csv file contents which read by notepad++.

Can you give me any idea for this matter?

Thanks

Id,Age,Job,Marital,Education,Default,Balance,HHInsurance,CarLoan,Communication,LastContactDay,LastContactMonth,NoOfContacts,DaysPassed,PrevAttempts,Outcome,CallStart,CallEnd,CarInsurance

4001,25,admin.,single,secondary,0,1,1,1,NA,12,may,12,-1,0,NA,17:17:42,17:18:06,NA

4002,40,management,married,tertiary,0,0,1,1,cellular,24,jul,1,-1,0,NA,09:13:44,09:14:37,NA

[Tom · Accepted solution]

Don't use an informat specification in the INPUT statement.  Use an INFORMAT statement to let SAS know that it needs to use a special method to read those variables.  If you do include the informat specification in the INPUT statement then use the colon modifier to prevent using formatted input mode for those variables.

You should probably add the DSD option to allow for null values.

You also might want to create a user defined informat to deal with those NA values in numeric fields.

proc format ;
invalue na 'NA'=. other=[32.];
run;

data test1;
length
  Id $4 Age 8 Job $20 Marital $20 Education $20 Default 8 Balance 8 HHInsurance 8
  CarLoan  8 Communication $20 LastContactDay 8 LastContactMonth $3
  NoOfContacts 8 DaysPassed 8 PrevAttempts 8 Outcome 8
  CallStart 8  CallEnd 8 CarInsurance 8
;
infile cards dsd truncover ;
input id -- carinsurance;
informat _numeric_ na. CallStart CallEnd time. ;
format CallStart CallEnd time8. ;
cards;
4001,25,admin.,single,secondary,0,1,1,1,NA,12,may,12,-1,0,NA,17:17:42,17:18:06,NA
4002,40,management,married,tertiary,0,0,1,1,cellular,24,jul,1,-1,0,NA,09:13:44,09:14:37,NA
;;;;

[eunbi]

Thank you Tom! Your solution works very well.

By the way, I have a question.

" If you do include the informat specification in the INPUT statement then use the colon modifier to prevent using formatted input mode for those variables."

Here, Actually I used colon modifier for date variables but i keep faced "."(dot) result.

CallStart : time11. CallEnd: time11.

like this.

Am I did correct? Or wrong??

Thank you for your advice !

[Tom]

If you add the : modifiers to your original INPUT statement then it works for the two lines you posted. It might be other lines where it doesn't work. 

You didn't include the TRUNCOVER (or the older less useful MISSOVER) option so if one of the line has too few values SAS will move to the next line and get out of sync.

As I said you also didn't include the the DSD option so multiple commas will be treated as a single delimiter just like multiple spaces would be if you left the default delimiter of a space.  So if one of the empty values is represented by adjacent comma then the input will also get out of sync.

908  data test1;
909  infile cards dlm=',' ; * dsd truncover ;
910  input Id Age Job $ Marital $ Education $ Default Balance HHInsurance CarLoan
911   Communication $ LastContactDay LastContactMonth $ NoOfContacts
912   DaysPassed PrevAttempts Outcome $ CallStart :time11. CallEnd :time11. CarInsurance $;
913   format CallStart time8. CallEnd time8.;
914  cards;

NOTE: The data set WORK.TEST1 has 2 observations and 19 variables.

                                                                              Car
Obs   Id   Age    Job     Marital  Education  Default  Balance  HHInsurance  Loan  Communication

 1   4001   25  admin.    single   secondar      0        1          1         1     NA
 2   4002   40  manageme  married  tertiary      0        0          1         1     cellular

       Last     Last
     Contact   Contact     NoOf      Days      Prev                   Call                 Car
Obs    Day      Month    Contacts   Passed   Attempts   Outcome      Start    CallEnd   Insurance

 1      12       may        12        -1         0        NA      17:17:42   17:18:06      NA
 2      24       jul         1        -1         0        NA       9:13:44    9:14:37      NA

[eunbi]

Appreciate so much details explanation!

I will keep study and learn more!

Thank you again!