DATA Step, Macro, Functions and more

Data Values Not Fully Loaded in Import

Reply
New Contributor
Posts: 4

Data Values Not Fully Loaded in Import

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!

Super User
Super User
Posts: 7,074

Re: Data Values Not Fully Loaded in Import

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?

New Contributor
Posts: 4

Re: Data Values Not Fully Loaded in Import

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;
Super User
Super User
Posts: 7,074

Re: Data Values Not Fully Loaded in Import

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.

Super User
Posts: 19,855

Re: Data Values Not Fully Loaded in Import

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;

 

Super User
Posts: 11,343

Re: Data Values Not Fully Loaded in Import

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.

New Contributor
Posts: 4

Re: Data Values Not Fully Loaded in Import

Thank You! My problems sloved.

Respected Advisor
Posts: 4,173

Re: Data Values Not Fully Loaded in Import

[ Edited ]

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

New Contributor
Posts: 4

Re: Data Values Not Fully Loaded in Import

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

Ask a Question
Discussion stats
  • 8 replies
  • 134 views
  • 2 likes
  • 5 in conversation