BookmarkSubscribeRSS Feed
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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;


4 REPLIES 4
Reeza
Super User

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.

Tom
Super User Tom
Super User

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;

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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.

SASKiwi
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 730 views
  • 0 likes
  • 4 in conversation