topic Re: Proc import for XLSX files, controlling variable name with getnames=no in SAS Programming

Proc import for XLSX files, controlling variable name with getnames=no

BruceBrad — Mon, 04 Dec 2023 02:46:19 GMT

I want to read a named range of data from an xlsx file with getnames=no. It is a single column of numbers. How do I control the variable name that gets assigned to the data? It seems to be using the column name for the data (eg B if the range is in column B). However, the range is in different columns in different spreadsheets, so I don't know this in advance.

Re: Proc import for XLSX files, controlling variable name with getnames=no

Patrick — Mon, 04 Dec 2023 07:25:59 GMT

Is this a named range or do you actually provide the range explicitly to Proc Import?

If it's an explicit range then you know the column and you could simply rename the column in the out statement: out=work.want(rename=(e=wantVarName))

If these are named ranges then there are other options possible.

To provide "the right" solution for this case if necessary: Are you running a set of Proc Imports and then got a set of tables that follow a naming convention but with always a single column with different name? Like: Table_1 with column C, Table_2 with column E, etc.

Re: Proc import for XLSX files, controlling variable name with getnames=no

BruceBrad — Mon, 04 Dec 2023 08:10:51 GMT

Your last description sums it up. I don't know which column the named range will be referring to. I have now sorted out a work-around. A bit clunky, but I can rely on the range being just a single column, and so the imported datafile will only have one variable. Then I can use this code to pull out the name of the variable.

%macro FirstVName(data);
%* returns the name of the first variable in the file;
%let dsid = %sysfunc(open(&data,i));
%sysfunc(varname(&dsid,1))
%mend FirstVName;

Re: Proc import for XLSX files, controlling variable name with getnames=no

Patrick — Mon, 04 Dec 2023 12:03:14 GMT

@BruceBrad I was thinking about something similar to what you have done.

In case you create this set of tables that all have a common "root" name and you want to combine the data into a single result table then below approach should also work. Using a SQL UNION will also ensure that the length of your variable will be the max length on any of the source variables - which when using Proc Import could differ between the tables.

/* create sample data */
data table_1(keep=E) table_2(keep=B) table_3(keep=F);
  E='A';
  B='XXX';
  F='9';
run;

/* generate SQL to combine the tables */
proc sql;
  select 
    'select * from '||cats(libname,'.',memname) 
      into :genSQL separated by ' union all '
  from dictionary.tables
  where libname='WORK' and memname like 'TABLE^_%' escape '^'
  ;
quit;

/* create zero row table structure with desired column name and attributes */
data table_structure;
  stop;
  attrib my_var length=$1;
run;

/* concatenate data */
proc sql;
  create table want as
  select * from table_structure
  union all 
  &genSQL
  ;
quit;

proc contents data=want;
run;

Or here another option

data want;
  set table_:;
  array vars {*} _character_;
  length new_var $3;
  new_var=coalescec(of vars[*]);
  keep new_var;
run;

Re: Proc import for XLSX files, controlling variable name with getnames=no

Tom — Mon, 04 Dec 2023 14:46:45 GMT

@BruceBrad wrote:

Your last description sums it up. I don't know which column the named range will be referring to. I have now sorted out a work-around. A bit clunky, but I can rely on the range being just a single column, and so the imported datafile will only have one variable. Then I can use this code to pull out the name of the variable.
%macro FirstVName(data);
%* returns the name of the first variable in the file;
%let dsid = %sysfunc(open(&data,i));
%sysfunc(varname(&dsid,1))
%mend FirstVName;

Exactly. You can ask SAS to find the names once it has made the dataset.

For your example of making a series of datasets you could just use PROC CONTENTS or DICTIONARY.COLUMNS to get the information you need to generate the code.

For example you could generate DSNAME and RENAME strings from DICTIONARY.COLUMNS

proc sql;
create table varlist as
  select memname as dsname length=32
        , catx('=',name,'want') as rename length=65 
  from dictionary.columns
  where libname='WORK' and memname like 'TABLE%' 
    and varnum=1
;
quit;

Then use that to generate the code:

data _null_;
  set varlist end=eof;
  if _n_=1 then call execute('proc datasets nolist lib=work;');
  call execute(catx(' ','modify',dsname,';rename',rename,';run;'));
  if eof then call execute('quit;');
run;