Proc Import giving "VAR1-VARn" 2

[SPhill]

Hello, I've been given a few CSV type files I need to read into SAS, normally I'd use Proc Import and have no problems however there's some extra lines in these CSV type files which seem to baffle proc import.

Here's an example:-

blah blah blah blah blah
blah_blah, blah
blah, blah
blah,T1,I,I,I,I
!,SPCODE,POLREF,INRREF,COVREF,AGE_AT_ENTRY
*,1,91000074,1,1,26
*,2,91000077,1,1,26

I've substituted some descriptions with blah as it might be confidential. If I use:-

proc import datafile="C:\SP\test.rpt"
OUT=cup
dbms=csv replace;
getnames=yes;
datarow=5;
run;
proc print data=cup;
run;

I end up with VAR1 to VAR6 as my variable names instead of ! SPCODE POLREF INRREF COVREF AGE_AT_ENTRY. No matter where I place my datarow I end up with VAR1 to VAR6. Manually inputting variables with infile isn't an option as there's about 260 vars, also they have different layouts between csv files.

Is there a way round this or are my CSV files junk and SAS cannot read them in.

Thanks

SP

SAS v8.2

 

[klaz2002]

Why not use the INFILE statement in a datastep.

(This is how we used to read CSV files before the proc import days. )

filename in 'c:\temp.csv';

data test;
  infile in dlm=',' missover dsd lrecl=32767 firstobs=3;
   informat test $6. ;
   informat test2 $5. ;
   informat test3 $5. ;
   format test $6. ;
   format test2 $5. ;
   format test3 $5. ;

   input test $ test2 $ test3 $ ;
run;

This option works but you must write all the vars that you need to import. The easiest method would be to get rid of the 'clutter' and then use the proc import to import the CSV data.

Klaz

 

[SPhill]

Thanks for the quick reply, I'm afraid I can't use INFILE as there's too many VARS and multiple CSV files with different column headers to set up a generic programme to cover them all. Basically the CSV files I've been given aren't any good they've specifically been setup for another (tedious) software package to read in, I think it uses the '!' as it's start point. I was just hoping SAS could handle it with PROC IMPORT and DATAROW as it's all automatic. I was wondering if there's a way of ditching my VAR1 to VARn variables and force it to look at the line obs1 which contain the column headers and use them for my VARS. Cleaning the CSV files will create duplicates which we are trying to avoid.

SP

 

[klaz2002]

You could 'read' in the file and pre-process it. Then have SAS use proc import in the normal way. You state that the '!' symbol is used to let your program know that real data follows. Well why not try this approach/ See below

*** YOUR TEMP FILE ***;
filename tmp 'c:\temp\_a.dat';
*** YOUR CSV FILE (THIS WILL CHANGE FOR EACH NEW CSV FILE) ***;
filename in  'c:\temp\a.csv';

data _null_;
 infile in;
 file tmp;
 length intext $2000;
 retain i 0;

 input intext &;
 if index(intext, '!') gt 0 then
  i = 1;

 *** ONLY OUTPUT THE DATA AFTER THE ! CHAR IS FOUND ***;
 if i=1 then do;
   put intext;
 end;
run;

*** NOW USE PROC IMPORT IN THE NORMAL WAY ***;
proc import 
  datafile = "%SYSFUNC(PATHNAME(tmp))" 
  OUT      = cup 
  dbms     = csv replace;
  getnames = yes;
  datarow  = 2;
run;

Let me know if this works.
Klaz

 

[SPhill]

Great Klaz this works fine, should be a solution to our needs, had to put a couple of 'LRECL=3000' comments at the end of my filenames to make sure I'm getting all the columns but it works a treat.

Thanks very much.

SP

 

[ChrisW75]

I believe that there are also some options within Proc Import to specify which line is the first data line, and which line to use as the header...

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[SPhill]

Thanks for the interest CW, do you mean 'getnames' and 'datarow', I tried them, didn't give us the result we were looking for as in my post '11 Sep 07 11:53' at the top. Are there any other options available within proc import?

SP

 

[ChrisW75]

Sorry, I missed that, yes, those were the options I was thinking of.
Klaz's method is the best then.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.