BookmarkSubscribeRSS Feed
Calcite | Level 5



I have to import a CSV (encoding in UTF-8) file from an HTTP address. One problem is the fact that, in the 2nd column, there is some numeric values (1->99) and at the end of this column, there is some character (A->ZZ). Theses characters are replaced by . during this importation. 


How can I solve this problem ? 


Please find below my importation code: 


filename elect url ""

proc import datafile=elect out=elect replace dbms=csv ; 
delimiter = ";";
Diamond | Level 26 RW9
Diamond | Level 26

This is not a Comma Separated Variable file!!  Aaargh.  Sorry, its just every post here now states I am importing a CSV file, and then proceed to describe a file which does not comply with the definition of a comma separated variable file.  You are importing a delimited file, the delimeter is a semi-colon.  So first off, lets forget about CSV.  


Next issue, your using proc import.  This proceure is a guessing procedure, it scans some data and then tries to guess what to import the data as.  It is not a good idea to use this in any production process, as the output cannot be considered consistent.  


Basing on your data specification, or data transfer agreement, you can build a datastep import program which will always (i.e. is repeatable and finite) produce the same results given the same data:

data want;
  infile ".../...tour.dlm" dlm=";";
  length ...;
  format ...;
  informat ...;
  input ...;

With this you specify what data to read in, how to read it in, based on your superb knowledge of the data in question, so you would know that column 1 is character, and thus you can write:

data want;
  infile ".../...tour.dlm" dlm=";";
  length tour $200 ...;
  format tour $200 ...;
  informat tour $200 ...;
  input tour $ ...;

Now sure you might not want to write each one, so look at the log generated by the proc import you have, you will see it has written a datastep import for you, however its guesses are wrong based on the data, so you can copy that program, into your program, and them modify it to correctly read the data. 

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
  • 1 reply
  • 2 in conversation