Proc Import didn't read all columns

2
votes

I have a mer file containing 2500 observations and 300 variables. I used proc import to read into SAS, but it only reads in 200 variables and 2500 observations. The log stated the import was successful.

I've tried "getnames=no" and that resulted in the import of 300 variables, but the first row contains variable names, so I would like to keep that as "getnames=yes". I've tried converting the mer file to csv and doing proc import, but I keep getting errors. Any ideas on what I'm doing wrong? I'm using SAS 9.3.

        proc import datafile="XXX.mer"
        out=xx.xxxxx dbms=dlm replace;
        delimiter=",";
        getnames=yes;
        datarow=2;
        guessingrows=5000;
        run;
4
sas
Do you have access to Enterprise Guide? I find that its data import task can be a great debugging tool for PROC IMPORT. It sounds like you might have an issue with the variable names in the variable names row of your csv file. Can you see if the first record it outputs with getnames=no has all of the correct variable names? If you have any SAS-invalid variable names (e.g. starts with a number, has a space, etc.), you may want to turning on options validvarname=any and see if that alleviates the issue. - Stu Sztukowski
I do not have Enterprise Guide. With getnames=no the first record it outputs has all the correct variable names, however I know some of my variable names have been truncated (which is ok with me but not sure if that is causing the issue). - Norman Wong
@StuSztukowski I tried 'options validvarname=any' and it still resulted in the import of 200 instead of 300 variables. - Norman Wong
I wonder if you have a linefeed in the first row, and/or if the variable names are too long on the first row such that you go past the LRECL (32767). We probably would need more information about exactly what happens. Is it precisely the 200th variable that is read in, no more? Or 204? Can you see anything interesting about the last variable read in and/or the first variable not read in? - Joe
Hmm. 28*204=5704 so not likely over the LRECL. If there aren't funny characters (and you may want to verify that there aren't!) you should probably open a ticket w/SAS. - Joe

4 Answers

0
votes

Norman:

You can use a small SAS program to show you the context around control characters in the header line of the data file. You should see 0D 0A and maybe 09

data _null_;
  infile "XXX.mer" recfm=n end=end;

  retain cc_context mark;

  length c $1;
  input c $char1. ;

  if not mark and c < '20'x then mark = _n_;

  length cc_context $20;

  if _n_ < lengthc(cc_context)
    then substr(cc_context,_n_,1) = c;
    else cc_context = substr(cc_context||c,2,20);  

  if _n_ = mark + 10 then do;
    length info $150;
    info = cc_context;

    p = ANYCNTRL(info);
    do while (p);
      c = substr(info,p,1);
      cx = '<' || put(c,$hex2.) || '>';
      info = tranwrd(info,c,trim(cx));
      p = ANYCNTRL(info);
    end;

    put info;
    cc_context = '';
    mark = 0;
    mark_count + 1;
    if mark_count >= 10 then stop; * limit the review;
  end;
run;
0
votes

Did you try telling PROC IMPORT to read the file without the column names?

proc import datafile="XXX.mer"
  out=xx.xxxxx dbms=dlm replace;
  delimiter=","
;
  getnames=no;
  datarow=2;
  guessingrows=5000;
run;

You can then read the values from the first row and use them as labels.

filename code temp;
data labels ;
  infile "XXX.mer" dsd obs=1  ;
  length varnum 8 name $32 label $256 ;
  do varnum = 1 by 1 ;
    input label @;
    name = cats('VAR',varnum);
    output ;
    file code ;
    put 'LABEL ' name '=' label :$quote. ';' ;
  end;
run;
proc datasets lib=XX nolist;
  modify XXXXX;
  %include code / source2;
run;
0
votes

Simple solution to your problem is as per below 1) Split the file in 2 parts column 1- 200 and 201-till the end, make sure both file should have some common keys so that you can merge them later 2) import them in two separate datasets 3) merge the dataset using proc sql and with the help of the common keys.

0
votes

I got the same problem here and this works to me:

PROC IMPORT OUT= &BASE_SAIDA. DATAFILE= "bla bla bla"
            DBMS=CSV REPLACE; 
            **delimiter='09'x;** 
            GETNAMES=YES;
RUN;

When I user DELIMITER = '09'x it returns all columns.