Thanks, this also solves how to automatically process dynamic data sets where the owners may add and drop variables. Added code to automatically clean up into a usable variable name. options validvarname=any; proc import datafile = 'Participants.xlsx' out = participants dbms = xlsx replace; getnames=YES; run; proc contents data=participants out=contents(keep=name) noprint; run; data new_names; set contents; length name_new $32.; lead_val=rank(substr(strip(name),1,1)); if ((lead_val>=65 & lead_val<=90) | (lead_val>=97 & lead_val<=122)) then lead_ok=1; name_new=prxchange("s/[^a-zA-Z0-9]/_/", -1, strip(name)); if (missing(lead_ok)) then name_new="_"||name_new; name_new=tranwrd(name_new,"___","_"); name_new=tranwrd(name_new,"__","_"); run; proc sql noprint; select nliteral(name)||"="||name_new into :renam_list separated by ' ' from new_names; quit; data participants_new; set participants; rename &renam_list; run;
... View more