How to import sas table using proc import

Question

Hi everyone i am trying to import a sas table with proc import statement as headers are in record2 i am using the following code

           proc import datafile="WORK.FINAL_1"
           out= datatest
           dbms=csv(what should be this)
           replace;
           datarow=2;

Thanks

Answer 1

Assuming you actually have a CSV file (which is form of a delimited text file) but that the problem is that your file has an extra line before the header row. (Note: The header row is one with text strings instead of variable values. These strings are usually used as the names for the variables.)

Let's make an example "bad" csv file by first dumping SASHELP.CLASS to a CSV file and then copying the good CSV file to a new file with an extra row at the top.

filename csv temp;
filename badcsv temp;
proc export data=sashelp.class dbms=csv file=csv replace ;
run;
data _null_;
  infile csv;
  file badcsv ;
  if _n_=1 then put 'This is an extra line';
  input;
  put _infile_;
run;

If you have such a "bad" CSV file then you cannot use PROC IMPORT to read it directly because PROC IMPORT will always use the FIRST line as the source for guessing how to name the variables, no matter where you tell it to start reading the datalines.

Here are some ways to deal with such a file.

The easiest is to skip the PROC IMPORT and just write your own data step to read the file:

data want;
  infile badcsv dsd truncover firstobs=3;
  input Name :$8. Sex :$1. Age Height Weight ;
run;

Or you can read from the third row while telling PROC IMPORT not to try to get names from the file. And then rename the variables later. You can even use PROC IMPORT to read the second line to get strings to use as the names.

proc import file=badcsv dbms=csv out=nonames replace ;
  getnames=NO;
  datarow=3;
run;
proc import file=badcsv dbms=csv out=headers replace ;
  getnames=NO;
  datarow=2;
run;
proc transpose data=headers(obs=1) out=names ;
  var _all_;
run;

proc sql noprint; 
  select catx('=',nliteral(_name_),nliteral(col1))
    into :renames separated by ' '
    from names
  ;
quit;
data want;
  set nonames;
  rename &renames;
run;

Or you could make a copy of the CSV file without the extra lines and then you could use PROC IMPORT and it will be able to guess how to name the variables based on the header row.

filename goodcsv temp;
data _null_;
   infile badcsv firstobs=2;
   file goodcsv;
   input;
   put _infile_;
 run;
 proc import file=goodcsv dbms=csv out=want replace;
 run;

Or you could use a tool that can guess how to read the file and take the names from somewhere other than the first line. Like %csv2ds().

%csv2ds(filen=badcsv,out=want,namerow=2,datarow=3,replace=yes)