how to import data specs (excel) onto program

[HitmonTran]

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?

[JosvanderVelden]

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

[Amir]

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.

[Tom · Accepted solution]

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;

[ballardw]

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.