Help using Base SAS procedures

Proc Import Statement Issue

Reply
Regular Contributor
Regular Contributor
Posts: 238

Proc Import Statement Issue

I am trying to import a .csv and I have gone into the file and manually reformatted how it should come into SAS to run with other tables and their formats. My problem is no matter if I change and save or change and save as, the field still shows as numeric. I need it a char. I thought I could just change it on import.

PROC IMPORT

datafile=&xyx_summary

out=xyx_scores

dbms=dlm replace;

delimeter=' ,';

gernames=yes;

datarow=2;

guessingrows=250;

run;

Is there a way to state within this code 1 of the fields as a char? If not then can I do a format on that field to format=$char10 on the proc sql;


Super User
Posts: 17,737

Re: Proc Import Statement Issue

When you run the proc import code look at your log. It will have the import as a data step.  Copy and paste that code into the editor and change the format and informat of the variable you'd like read in as a character.

Super User
Super User
Posts: 6,495

Re: Proc Import Statement Issue

Do the variables (columns) in your delimited file actually change?

You will have more control if you just write the data step to read the file yourself.  You can recall the code generated by PROC IMPORT if it helps, but usually you can write much simpler, clearer code yourself.  For example if you file has three variables, the first of which is numeric and the other two are character with maximum lengths of 30 and 20, respectively, then your code could look like this:

data xyx_scores;

  infile &xyx_summary dlm=',' dsd truncover firstobs=2 ;

  length var1 8 var2 $30 var3 $20 ;

  input var1 -- var3 ;

run;

Regular Contributor
Regular Contributor
Posts: 238

Re: Proc Import Statement Issue

We used to do an auto infile where it just read the columns by ",". A comma .csv. Nothing apparently has changed with the layout of this file. We thought perhaps because of this large description file that has commas between the " " that was causing anything between commas in the 1 column to write as another column. However, the creators of file stated nothing has changed and you have never had issues in the past, which is true. So the girl that normally does this process found that something with SAS infile importing has changed with a recent update we received. We have to now figure out what so we can configure the auto infile. We don't want to have to swtich to a manual because there are 97 columns that have to be imported from this file that vary from num to char and vary in size. We really just want everything to import as in the past but say hey when you get to column 45, the column in question, read it this way instead so the import will be correct upon review.

Super User
Posts: 3,100

Re: Proc Import Statement Issue

The most likely cause of your problem is that the first character value held in column 45 is beyond the guessingrows = 250 limit. Check your input CSV - what row of column 45 has the first character value? You could adjust the guessingrows option to cater for this, but it will slow down your import. One quick and dirty option is to add a dummy row of data at the start of the CSV to get guessingrows to work correctly, then delete it in your program later.

Ask a Question
Discussion stats
  • 4 replies
  • 206 views
  • 0 likes
  • 4 in conversation