Create Program Code out of excel table in SAS

Question

For a Macro, I want to create an automated ATTRIB Statement.

I have an exel Table with all variable names, formats, lables and lengths.

Now I want SAS to read each row and pass it into:

%LET Format_VARIABLE = FORMAT = &For  LENGTH = &len LABEL = "&lab";

Any ideas how to archive this?

Answer 1

Assuming you have a dataset (called metadata) containing all of you variable names (vname), formats (vfmt), lengths (vlen) and labels (vlbl) from Excel :

/* Create VNAME1-VNAMEx, VFMT1-VFMTx etc */
data _null_ ;
  set metadata end=eof ;
  call symputx(cats('VNAME',_n_),vname) ;
  call symputx(cats('VFMT',_n_),vfmt) ;
  call symputx(cats('VLEN',_n_),vlen) ;
  call symputx(cats('VLBL',_n_),vlbl) ;
  if eof then call symputx('VNUM',_n_) ;
run ;

%MACRO BUILD_ATTRIB ;
  /* Iterate over each set of macro variables and resolve into `attrib` statement */
  attrib %DO I = 1 %TO &VNUM ;
         &&VNAME&I format=&&VFMT&I length=&&VLEN&I label="&&VLBL&I"
         %END ;
         ;
%MEND ;

/* To use in a datastep */
data want ;
  %BUILD_ATTRIB ;
  set have ;
run ;