CODEWITHSUNDEEP
importsasdatasetproc
Heng
Heng

Reputation: 41

SAS proc import guessingrows issue

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 !

Upvotes: 0

Views: 9319

Answers (2)

crow16384
crow16384

Reputation: 597

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;

enter image description here

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;

enter image description here

Upvotes: 2

Richard
Richard

Reputation: 27516

You can change the column type of a column that has all missing value by dropping it and adding it back as the other type.

Example (SQL):

filename csv 'c:\temp\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;

proc sql;
  alter table have
    drop c, d
    add c num, d num
  ;

Upvotes: 0

Related Questions