topic Data Values Not Fully Loaded in Import in SAS Programming

Data Values Not Fully Loaded in Import

Me_Caboo — Wed, 20 Sep 2017 22:50:39 GMT

Hi,

I am using a Proc Import statetment to import a CSV file. The Data has 20 variables 2 of the variables are Currencies. All the variables are fully imported, however the only the first column of currencies is imported fully. The second column containing currencies only imports the first digit not the enitre value.

For example:

Currency1 Currency2

$1000 3

$40 2

$500 1

$56 8

Should Read:

Currency1 Currency2

$1000 $30

$40 $200

$500 $15

$56 $8750

Are those values being truncated for any reason? Any reccomanation how to import the data fully?

Thank You!

Re: Data Values Not Fully Loaded in Import

Tom — Wed, 20 Sep 2017 23:01:01 GMT

How did you run the import? What does the data actually look like (the raw CSV file lines and not what it looks like after being opened by Excel). What end-of-line characters are in your file.

Why not just read the file directly, especially if it only has two columns, instead of using proc import?

Re: Data Values Not Fully Loaded in Import

Me_Caboo — Thu, 21 Sep 2017 01:31:33 GMT

proc import datafile="C:\sample.csv"
     out=sample
     dbms=csv
     replace;
     guessingrows=10;
run;

The original file is a .dat file converted into a .csv file. Could there be an issue during the file conversion?

DATA sample;
   INFILE 'c:\sample.dat';
   INPUT Var1 $ 1-20 Var2 $ 21-24 Var3 $ 25-28
         Var4 $ 29-31 Var5 $ 32-34 Var6 35-37 Var7 38-40;
RUN;

Re: Data Values Not Fully Loaded in Import

Tom — Thu, 21 Sep 2017 01:45:21 GMT

I assume the truncation is because of this statement in the PROC IMPORT code.

     guessingrows=10;

Use a larger value for the guessingrows option so that PROC IMPORT uses more information before it guesses what format to use for your variables.

Re: Data Values Not Fully Loaded in Import

Reeza — Thu, 21 Sep 2017 03:01:26 GMT

When you did the conversion from DAT to CSV did you add a delimiter and quote the values?

How did you do the conversion?

@Me_Caboo wrote:
proc import datafile="C:\sample.csv"
     out=sample
     dbms=csv
     replace;
     guessingrows=10;
run;
The original file is a .dat file converted into a .csv file. Could there be an issue during the file conversion?
DATA sample;
   INFILE 'c:\sample.dat';
   INPUT Var1 $ 1-20 Var2 $ 21-24 Var3 $ 25-28
         Var4 $ 29-31 Var5 $ 32-34 Var6 35-37 Var7 38-40;
RUN;

Re: Data Values Not Fully Loaded in Import

ballardw — Thu, 21 Sep 2017 14:15:59 GMT

Proc Import when reading CSV will create datastep code showing how the set was actuall read.

It always pays to look a that, if not copy from the editor and modify it to read the data as expected.

The generated code will have an Informat and Format statement for each variable. If you look at the code you may find that the informat for currency2 was something like $1. which would occur if the first 10 rows of data for that variable were blank.

If you have that generated code modify it to use the same informat and format as currency1 used.

Re: Data Values Not Fully Loaded in Import

Me_Caboo — Fri, 22 Sep 2017 01:10:34 GMT

Thank You! My problems sloved.

Re: Data Values Not Fully Loaded in Import

Patrick — Fri, 22 Sep 2017 11:14:10 GMT

@Me_Caboo

What has been the solution? Please share!

And if one of the answers has been the solution for you then please mark this answer as solution.

Re: Data Values Not Fully Loaded in Import

Me_Caboo — Fri, 22 Sep 2017 12:05:30 GMT

Increase guessingrows to 1000 (guessingrows= 1000)....