How to read whole columns from excel using their header name in SAS

[skynet]

I have one excel file which has multiple columns, I want to make SAS code which can populate data from particular column to user define variable. All column have headers.

[Tom]

---

@skynet wrote:

I have one excel file which has multiple columns, I want to make SAS code which can populate data from particular column to user define variable. All column have headers.

---

Please explain more what you mean.  If you just use normal SAS code for reading from an Excel worksheet you will get a dataset that has one variable for every column in the sheet.  The name of the variable will be based on the value in the first row (the "header").

What would be the purpose to trying to make a dataset that only had one of the variables?  And if you needed that you could easily do that after you have the data out of the worksheet and into a SAS dataset.

To read the first worksheet in the Excel file (XLSX file) you could use code like this:

proc import dbsm=xlsx file='myfile.xlsx' our=want replace;
run;

If you want to read other sheets from the file (or only parts of one sheet) then look at the options for PROC IMPORT such as either the SHEET= or RANGE= statements.

[skynet]

I have SAS macro ready which consumes Dir, file, var, data as input from user. But I have number of files and have to add these inputs manually which is time consuming when have to process 100s of file with macro. So, I have created excel sheet whose headers are same as macro's input and want to create SAS code which can read my excel and parse that data to macro and then macro will get triggered and I can get final outcome.

Dir	file	var	data
Dir1	file1	var1	data1
Dir2	file2	var2	data2
Dir3	file3	var3	data3
Dir4	file4	var4	data4

I have excel like above and have SAS macro ready.

[ballardw]

If you have the values in a data set then a data step using that data set with the CALL EXECUTE statement can submit calls to one or more macros. Or possibly better for debugging purposes write the generated macro calls to a program text file and use %include to call the file.

Example of the second approach. Suppose I have a macro named Dummymacro that does something based on values of StudentName and StudentSex variables. A call to the macro might look like

%dummymacro (studentname=Alfred,studentsex=M) 

Using the SAS supplied data set which contains names and a variable that happens to match the requirements of the Studentsex macro variable I could use:

data _null_;
   file print;
   /* file "<path>\examplecode.sas";*/
   set sashelp.class;
   length longstr $ 200;
   longstr = cats('%dummymacro (studentname=',name,',studentsex=',sex,')');
   put longstr;
run;

The File Print will write to the results window by default so you can see what is generated.

Once happy with that appearance uncomment the other File statement and provide a valid path and name for the code to be generated.

To execute the generated code I would use %include matching the file name used in the data step.

%include "<path>\examplecode.sas";

[Tom]

---

@skynet wrote:

I have SAS macro ready which consumes Dir, file, var, data as input from user. But I have number of files and have to add these inputs manually which is time consuming when have to process 100s of file with macro. So, I have created excel sheet whose headers are same as macro's input and want to create SAS code which can read my excel and parse that data to macro and then macro will get triggered and I can get final outcome.

Dir	file	var	data
Dir1	file1	var1	data1
Dir2	file2	var2	data2
Dir3	file3	var3	data3
Dir4	file4	var4	data4

I have excel like above and have SAS macro ready.

---

So EXCEL has nothing to do with your actual problem.  Get the data into a SAS dataset.  If you already have it in an EXCEL sheet then use PROC IMPORT.  Once you have it in a dataset use it from there.

It sounds like you want to use the data to generate calls to a macro.  You don't mention the name of the macro, just the names of the parameters, so let's assume macro is named MYMACRO so we can write some example code.

First let's convert the listing you shared into a SAS dataset (since we don't have access to your actual dataset.)

data have;
   input dir :$200. file :$200. var :$32. data :$200.;
cards;
Dir1 file1 var1 data1
Dir2 file2 var2 data2
Dir3 file3 var3 data3
Dir4 file4 var4 data4
;

Now we can use a data step to make a file that has one macro call per observation.

filename code temp;
data _null_;
  set have;
  file code;
  put '%mymacro(' dir= ',' file= ',' var= ',' data= ')';
run;

Which will make a file that looks like this:

%mymacro(Dir=Dir1 ,file=file1 ,var=var1 ,data=data1 )
%mymacro(Dir=Dir2 ,file=file2 ,var=var2 ,data=data2 )
%mymacro(Dir=Dir3 ,file=file3 ,var=var3 ,data=data3 )
%mymacro(Dir=Dir4 ,file=file4 ,var=var4 ,data=data4 )

Which we could execute by using the %INCLUDE statement.

%include code / source2;