BookmarkSubscribeRSS Feed
Me_Caboo
Calcite | Level 5

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!

8 REPLIES 8
Tom
Super User Tom
Super User

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?

Me_Caboo
Calcite | Level 5
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;
Tom
Super User Tom
Super User

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.

Reeza
Super User

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;

 

ballardw
Super User

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.

Me_Caboo
Calcite | Level 5

Thank You! My problems sloved.

Patrick
Opal | Level 21

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

Me_Caboo
Calcite | Level 5

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 8 replies
  • 1275 views
  • 2 likes
  • 5 in conversation