How to set a proc import file into a variable in a macro?

[melhaf]

Hi,

I want to create this (here is the example below)

	proc import 
		datafile ="C:\blah blah\TAbLE_OF_VARIABLES.xlsx"
		dbms = xlsx 
		out = TAbLE_OF_VARIABLES
		replace;
	run;

and then  use this table_OF_variables  in a %MACRO analysis ( 😞

%macro analysis();

%include "lots_of_testes.sas"

;%MACRO_TESTING (
date=&date.,
%etc=&etc.

but I want to call on this lots_of_variables.xlsx that has been stored in my work.lots_of_variables like this:

%macro analysis();
%include "lots_of_testes.sas"

;%MACRO_TESTING (
date=&date.,
%etc=&etc.

----> &lots_of_variebles=work.lots_of_Variebles

%mend analysis; 
%analysis;

some thing like that, but ofc it shouldn't be work.lots_of_varibles 

[PaigeMiller]

I get the feeling that this has been overcomplicated somehow (not unusual when someone first tries to use macros) and maybe macros are not even needed (again, not unusual when someone first tries to use macros). So, the next step is for you to take a step back and write, in words (not SAS code) what the problem is. Describe it clearly, as if you were describing it to your boss or university vice president, who doesn't know anything about SAS. Even I and others in this forum, who do know SAS, would benefit by understanding the problem, in words, not in SAS code. Please do this. Please describe the problem in words and not by referring to SAS code. This seems like the best approach to move forward.

Nevertheless, looking at the code you provided, I am confused. First you show this code

proc import datafile ="C:\blah blah\TAbLE_OF_VARIABLES.xlsx"
    dbms = xlsx out = TAbLE_OF_VARIABLES replace;
run;

and then you show this code

%macro analysis();
%include "lots_of_testes.sas"
;%MACRO_TESTING (
date=&date.,
%etc=&etc.

and there are many problems here. The biggest problem is that we don't really know what "lots_of_testes.sas" does, we would need to see the code in order to figure out how to incorporate TABLE_OF_VARIABLES into this code. We also don't know what macro TESTING does, and so again it is not possible for us to say how to incorporate TABLE_OF_VARIABLES into that macro. Lastly, this nested macro thing doesn't work well the way you have programmed it.

[melhaf]

"lots_of_testes.sas" is the overall parameters that apply to the "travel billing routine" for the company in question are specified.

[PaigeMiller]

---

@melhaf wrote:
"lots_of_testes.sas" is the overall parameters that apply to the "travel billing routine" for the company in question are specified.

---

Saying this to your university vice president or boss does not explain the problem you are trying to solve, and nor does it explain anything to me. This is the first time that "travel billing routine" has been mentioned and so is meaningless without more understanding. We still need to understand the big picture, not the details. Once we understand the big picture, then next the details become meaningful.

[melhaf]

Hi, I updeted my answer below

[melhaf]

wtsASFSA

[Quentin]

I'm also confused.

What is in Table_Of_Variables?  Can you show a PROC PRINT?  

Here's one guess at what you might be doing.  Is Table_Of_Variables a table with two columns, variable and value, like:

Variable	Value
x	1
y	2
z	5

?

And is it your goal to make three macro variables from that table?

If so, then you could do it something like:

data _null_ ;
  set Table_Of_Variables ;
  call symputx(Variable,Value,'L') ;
run ;

But as @PaigeMiller said, if you can describe the big picture, that will make it easier for people to help you.