Help with import data

Accepted Solution Solved
Reply
Super Contributor
Posts: 420
Accepted Solution

Help with import data

Hi Everyone,

I import csv data into SAS using my code. It works fine previously but this time few last variables turn out missing all data.

All variables are data number.

I attached the sample csv file.

Also I add truncover and DSD option to my code below.

However the problem still there with the last few variables.

Any help is very much appreciated.

Thank you,

HHC

data mt4;

infile 'E:\test.csv' truncover DSD

delimiter=';' ;

   input TimeFrame $ date :yymmdd8.  Hour_Minute $

  a1 a2 a3 a4 a5 a6 a7 a8 a9 a10 a11 a12 a13 a14 a15 a16 a17 a18 a19 a20 a21 a22 a23

  a24 a25 a26 a27 a28 a29 a30 a31

  ;

   format date yymmdd8.;

   run;

Attachment

Accepted Solutions
Solution
‎10-29-2013 02:03 PM
Super User
Super User
Posts: 7,078

Re: Help with import data

So your data lines look like they are longer than 256 so you should add the LRECL option to the INFILE statement to prevent SAS from truncating the lines as it reads them from the file. Check the notes in your SAS log.

View solution in original post


All Replies
Frequent Contributor
Frequent Contributor
Posts: 83

Re: Help with import data


Hi HHC

Did you check that may be the original data is a file mixed of  ; and , .

Thanks

Super User
Super User
Posts: 7,078

Re: Help with import data

Add the TRUNCOVER option to the INFILE statement. Otherwise when a line has fewer values SAS is moving to the next line to find values for those variables.

You should also add the DSD option to handle when there are missing values.  Without the DSD option this string would look to SAS like 2 values instead of three with he middle one missing.

1;;3

Respected Advisor
Posts: 3,799

Re: Help with import data

These messages

NOTE: Invalid data for mfi_24 in line 3 1-6.

NOTE: Invalid data for fractal_up in line 3 8-9.

indicate that you are not using LIST input for the variables named,  because line 3 column 1-6 contains a delimiter which should not happen.

otherwise these data lines

H4;2013.10.28;0:00;25.39263;51.39528;55.48431;0;0

H4;2003.04.07;4:00;0;0;0;0;0

H4;2003.04.07;8:00;0;0;0;0;0

should produce the correct input.  Show your exact INPUT statement.  The entire data step would be best.

Super Contributor
Posts: 420

Re: Help with import data

Posted in reply to data_null__

Thank you for your helps.

I updated my original post with a sample file and also add truncover and DSD in the code but still not help.

Thank you,

HCC

Solution
‎10-29-2013 02:03 PM
Super User
Super User
Posts: 7,078

Re: Help with import data

So your data lines look like they are longer than 256 so you should add the LRECL option to the INFILE statement to prevent SAS from truncating the lines as it reads them from the file. Check the notes in your SAS log.

Super Contributor
Posts: 420

Re: Help with import data

Thank you, Tom.

I got it now.

HHC

Super Contributor
Posts: 420

Re: Help with import data

Hi Tom,

One minor issue I had with the import is that when I put Input Hour_minute $, the next variable is imported properly and )of course) Hour_minute is character.
However, when I put Hour_minute time5. to make it time format, Hour_minute is imported properly BUT the next variable a1 turn into all missing value.

Then I play around with the code and when I delete the DSD, everything is imported nicely with time5. for Hour_minute.

I just curious why it is like that, if you could help.

Thank you,

HHC

data mt4;

infile 'E:\test.csv' truncover DSD

delimiter=';' ;

   input TimeFrame $ date :yymmdd8.  Hour_Minute $

  a1 a2 a3 a4 a5 a6 a7 a8 a9 a10 a11 a12 a13 a14 a15 a16 a17 a18 a19 a20 a21 a22 a23

  a24 a25 a26 a27 a28 a29 a30 a31

  ;

   format date yymmdd8.;

   run;

Respected Advisor
Posts: 3,799

Re: Help with import data

You have the answer in your input statement.  What do you notice about  the INFORMAT you used for DATE that you do no use for "Hour_minute time5."

You might also look at the documentation for the INPUT, list style and also at the INFORMAT statement.

Super Contributor
Posts: 420

Re: Help with import data

Posted in reply to data_null__

Thank you, data_null_.

HHC

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 305 views
  • 6 likes
  • 4 in conversation