topic Re: how to import data specs (excel) onto program in SAS Programming

how to import data specs (excel) onto program

HitmonTran — Fri, 29 Sep 2023 08:25:54 GMT

Hello, Not sure if this will be a simple process, but is it fairly simple to import an excel file (eg ADSL specs) and convert them into dataset variable names, length, and label?

Re: how to import data specs (excel) onto program

JosvanderVelden — Fri, 29 Sep 2023 08:48:43 GMT

Ex. https://youtu.be/HVfD3uJv5lc?feature=shared

Re: how to import data specs (excel) onto program

Amir — Fri, 29 Sep 2023 08:50:00 GMT

Hi,

SAS can read Excel files, an example taken from SAS blog Using LIBNAME XLSX to read and write Excel files is shown below:

/* because Excel field names often have spaces */
options validvarname=any;
 
libname xl XLSX '/folders/myfolders/sas_tech_talks_15.xlsx';
 
/* read in one of the tables */
data confirmed;
  set xl.confirmed;
run;
 
libname xl CLEAR;

Thanks & kind regards,

Amir.

Re: how to import data specs (excel) onto program

Tom — Fri, 29 Sep 2023 14:08:19 GMT

Is the question how to convert an Excel sheet into a SAS dataset?

Or is the question once you have dataset with variables like VARNUM, NAME, TYPE, LENGTH, FORMAT and LABEL how to use that to define a SAS dataset?

First convert data to ATTRIB statements.

filename code temp;
data _null_;
  set specs ;
  by varnum;
  length vname vlength vformat $60 ;
  vname=nliteral(name);
  if lowcase(type)='char' then vlength=cats('length=$',length);
  else vlength=cats('length=',max(8,length));
  if format ne ' ' then vformat=cats('format=',format);
  put 'attrib ' vname vlength vformat label=:$quote. ';' ;
run;

Then you use %INCLUDE to use those ATTRIB statements to define the dataset.

For example you could use a step like this to make an empty dataset with those variables.

data want;
  %include code / source2;
  call missing(of _all_);
  stop;
run;

Re: how to import data specs (excel) onto program

ballardw — Fri, 29 Sep 2023 15:28:08 GMT

For your "derived" variables you may want to have someone investigate CATX instead of strip()|| strip.

I have a minor concern that your derived variables are attempting to use variables that may not be defined in the order they occur. So this is likely going to take two or three passes through the junk to create appropriate attributes and the additional code that seems to be implied.