topic how to read .xlsx files as all character using sas 9.3? in SAS Programming

how to read .xlsx files as all character using sas 9.3?

dennis_oz — Sat, 06 Jun 2020 12:25:10 GMT

Requirement 1 -> read all multiple excel files as in multiple structures as character .

cannot convert it to csv

Re: how to read .xlsx files as all character using sas 9.3?

Kurt_Bremser — Sat, 06 Jun 2020 13:46:58 GMT

It is not possible in one pass. Any method (proc import or libname xlsx) will have its own idea about column attributes, so you will need to fix it in a follow-up step that converts all _numeric_ variables to character.

Re: how to read .xlsx files as all character using sas 9.3?

DavePrinsloo — Sat, 06 Jun 2020 17:48:23 GMT

The handling of reading xlsx was improved a lot in SAS 9.4 when xlsx engine was introduced. Do you really still have 9.3?

Re: how to read .xlsx files as all character using sas 9.3?

ballardw — Sat, 06 Jun 2020 20:16:19 GMT

@dennis_oz wrote:

Requirement 1 -> read all multiple excel files as in multiple structures as character .

cannot convert it to csv

Convert to Tab delimited and use the approach would use with CSV only with TAB delimiter.

(I suspect you meant "can't convert to any text file format that would actually allow this with 9.3")

And where did the "cannot convert" restriction come from? If you have a lot of files of the same structure it is much easier to deal with the text formats such as CSV because you can use wildcards in file names to read multiple files with a singe data step. Which does insure that all the like variables have the same properties for length.

If you mean to force a Proc Import approach then there is no way you can control that length issue other than inserting a common row of values at the top of each file. Which is likely to be more work than you expect.

Re: how to read .xlsx files as all character using sas 9.3?

Vince_SAS — Sat, 06 Jun 2020 22:57:17 GMT

Maybe something like this will work for you:

options validmemname=extend;

libname xl excel 'C:\temp\numeric.xlsx';

data work.character;
set xl.'sheet1$'n(dbsastype=('a'='char(10)'
                             'b'='char(10)'
                             'c'='char(10)'
                             'd'='char(10)')
                  );
run;

The EXCEL engine is supported only on Windows, and the bitness of SAS must match the bitness of Windows.

Vince DelGobbo

SAS R&D

Re: how to read .xlsx files as all character using sas 9.3?

Patrick — Mon, 08 Jun 2020 03:12:27 GMT

@dennis_oz wrote:

Requirement 1 -> read all multiple excel files as in multiple structures as character .

cannot convert it to csv

Except for the approach @Vince_SAS proposes - which I'm not sure is already available in SAS 9.3 - there is no direct way to read all source data into character variables only.

I believe if reading from an Excel source SAS will always attach a format to the created variables. If so then you could try if code as below does what you're after.

%macro convertAllVarsToChar(inds, outds=);
  %let inds=%upcase(&inds);
  %if &outds= %then %let outds=&inds;
  %local lib tbl;
  %let lib=%scan(WORK.&inds,-2,.);
  %let tbl=%scan(&inds,1,.);

  proc sql;
    create table want_meta as
    select *
    from dictionary.columns
    where libname="&lib" and memname="&tbl"
    order by varnum
    ;
  quit;

  filename codegen temp;
  data _null_;
  /*  file print;*/
    file codegen;
    set want_meta end=last;

    length nm_in nm_out $35;
    nm_in =cats("'",name,"'n");
    nm_out=cats("'_",substrn(name,1,31),"'n");

    if _n_=1 then
      do;
        put 
          "data &outds;" /
          @3 "set &inds;"
          ;
      end;

    if type='num' then
      do;
        if format='BEST.' then format='BEST16.';
        put @3 nm_out '= put(' nm_in ',' format ');';
      end;
    else
      put @3 nm_out '=' nm_in ';';
    put
      @3 'drop ' nm_in ';' /
      @3 'rename ' nm_out '=' nm_in ';' 
      ;
    if last then
      put 'run;';
  run;

  %include codegen / source2;
  filename codegen clear;
%mend;

proc import 
  datafile='~/test/test.xlsx'
  out=want
  dbms=xlsx
  replace
  ;
run;

title 'Before Conversion';
proc contents data=want;
run;

%convertAllVarsToChar(want);

title 'After Conversion';
proc contents data=want;
run;
proc print data=want;
run;