SAS proc import guessingrows issue

Question

I'm trying to import csv file to SAS using proc import; I know that guessingrows argument will determine automatically the type of variable for each column for my csv file. But there is an issue with one of my CSV file which has two entire columns with blank values; those columns in my csv file should be numeric, but after running the below code, those two columns are becoming character type, is there any solutions for how to change the type of those two columns into numeric during or after importing it to SAS ?

Here below is the code that I run:

proc import datafile="filepath\datasetA.csv"
out=dataA
dbms=csv
replace;
getnames=yes;
delimiter=",";
guessingrows=100;
run;

Thank you !

The quickest fix is to edit the csv file and put . in the first row where the ,, are. Change them to ,., — Richard
Thank you Richard, I'm not allowed to modify the original document, is there any other way of doing it ? — Heng
It seems you already know how the data should be defined. Why are you using PROC IMPORT? Why not just run a data step to read the file? — Tom

crow16384 crow16384 · Accepted Answer · 2020-11-19T21:08:01

Modifying @Richard's code I would do:

filename csv 'c:\tmp\abc.csv';

data _null_;
  file csv;
  put 'a,b,c,d';
  put '1,2,,';
  put '2,3,,';
  put '3,4,,';
run;

proc import datafile=csv dbms=csv replace out=have;
  getnames=yes;
run;

Go to the LOG window and see SAS code produced by PROC IMPORT:

data WORK.HAVE    ;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile CSV delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
    informat a best32. ;
    informat b best32. ;
    informat c $1. ;
    informat d $1. ;
    format a best12. ;
    format b best12. ;
    format c $1. ;
    format d $1. ;
 input
             a
             b
             c  $
             d  $
 ;
 if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro   variable */
 run;

Run this code and see that two last columns imported as characters. Check it:

ods select  Variables;
proc contents data=have nodetails;run;

Possible to modify this code and load required columns as numeric. I would not drop and add columns in SQL because this columns could have data somewhere.

Modified import code:

data WORK.HAVE    ;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile CSV delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
    informat a best32. ;
    informat b best32. ;
    informat c best32;
    informat d best32;
    format a best12. ;
    format b best12. ;
    format c best12;
    format d best12;
 input
             a
             b
             c
             d
 ;
 if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro   variable */
 run;

Check table description:

ods select  Variables;
proc contents data=have nodetails;run;

SAS proc import guessingrows issue

2 Answers