topic Re: Creating a SAS table from Excel file with data in sheet1 and Variable Labels in Sheet2 in SAS Procedures

Creating a SAS table from Excel file with data in sheet1 and Variable Labels in Sheet2

Sanchit — Tue, 07 Jun 2016 12:53:28 GMT

Hi SAS coders,

I'm a new learner of SAS, trying to create a SAS data set from excel file where data is in sheet 1 and Variable Labels are in sheet 2 in the following format:-

SHEET1: SHEET 2

Like this there are 20 columns and their variable labels. Is there a way I can create a data set without manually entering the Variable Labels using Label command? (Using SAS studio)

Re: Creating a SAS table from Excel file with data in sheet1 and Variable Labels in Sheet2

RW9 — Tue, 07 Jun 2016 13:14:58 GMT

Not sure this is a good example for learner to try and do. What you want is to dynamically create code based on some metadata which is a more advanced topic. However here is some code, first read in both sheets to datasets:

data _null_;
  set label_dataset end=last;
  if _n_=1 then call execute('data data_dataset; set data_dataset; label');
  call execute(' ',strip(variable),'="',strip(variable_label),'"');
  if last then call execute(';run;');
run;

What the above does is create a datastep with the label <variable>="<variable label>" for each row of your metadata sheet. So saves you typing it all. Again though, it is a more advanced topic, and the questions you should be asking is: is excel the best medium for data transfer, and why are column information on a different sheet?

Re: Creating a SAS table from Excel file with data in sheet1 and Variable Labels in Sheet2

Sanchit — Tue, 07 Jun 2016 18:21:44 GMT

Thanks for the help! 🙂