Desktop productivity for business analysts and programmers

Run macro on a list of values or a dataset containing table names

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Run macro on a list of values or a dataset containing table names

This is something I am working on:

1. I have a main dataset (A) on which I am applying filters to see the data that I have to edit manually.

2. Once the filtered datasets are created and I have edited the data manually in SAS EG, I want to update the main dataset (A) with the values from the filtered datasets. For this I am sorting the filtered datasets and then using data step and update t1 t2.

3. I have written a macro with proc sort in it and I can call it 10 times for 10 datasets and run the data step and update ten times to update the values.

 

I believe we can call a macro on an array. These are the steps I tried

1. I created a dataset tbl_names containing the table names, tried to create an array tbl_list and then call the macro in a do loop but it did not work.

 

%macro st(ds);
proc sort data = &ds;
by address_line_1;
run;
%mend;

 

data tbl_names;
input tbl_names1- tbl_names10 (: $35);
datalines;

t1 t2 t3;

run;

 

I used an example to define the above dataset tbl_names but this statement (input tbl_names1- tbl_names10 (: $35);) is currently not working. 

 

data _null_;
set tbl_names;
array tbl_list[*] tbl_names1- tbl_names10;
do i=1 to dim(tbl_list);
%st(tbl_list(i));
end;
run;

 

Parts of this code may not be working, I tried to re write but I was not able to. The code is just to give a picture of what I am trying to achieve.

 

Please let me know.

 

 


Accepted Solutions
Solution
a week ago
Super User
Super User
Posts: 8,261

Re: Run macro on a list of values or a dataset containing table names

[ Edited ]

The first error in your code is that you added a semi-colon to the end of your line of data.  SAS will treat any dataline with semi-colon on it as marking the end of the input and any other characters on that line are ignored.

 

The other errors or more about the logic of what you are trying to do. 

First why are you storing multiple dataset names in one observation instead of in many observations?  

data tbl_names;
  length table_name $41 ;
  input table_name @@ ;
datalines;
t1 t2 
t3
;

Note that 41 characters should be enough for an 8 character libref plus a period plus a 32 character member name.  

 

Second you cannot run a PROC SORT step in the middle of a DATA STEP.  So if you want to use a data step to generate code it will be code that runs AFTER the data step finishes.  

One way to do that is to use CALL EXECUTE().

data _null_;
  set tbl_names;
  call execute(cats('%st(',table_name,');'));
run;

But if your macro is complicated and uses macro variables it generates to control the logic of the macro then you will have a timing issue as CALL EXECUTE() will push the SAS statements the macro generates onto the stack to run.

If you add %NRSTR() then the macro call itself will be pushed onto the stack and timing issues will be avoided.

data _null_;
  set tbl_names;
  call execute(cats('%nrstr(%st)(',table_name,');'));
run;

 

View solution in original post


All Replies
Solution
a week ago
Super User
Super User
Posts: 8,261

Re: Run macro on a list of values or a dataset containing table names

[ Edited ]

The first error in your code is that you added a semi-colon to the end of your line of data.  SAS will treat any dataline with semi-colon on it as marking the end of the input and any other characters on that line are ignored.

 

The other errors or more about the logic of what you are trying to do. 

First why are you storing multiple dataset names in one observation instead of in many observations?  

data tbl_names;
  length table_name $41 ;
  input table_name @@ ;
datalines;
t1 t2 
t3
;

Note that 41 characters should be enough for an 8 character libref plus a period plus a 32 character member name.  

 

Second you cannot run a PROC SORT step in the middle of a DATA STEP.  So if you want to use a data step to generate code it will be code that runs AFTER the data step finishes.  

One way to do that is to use CALL EXECUTE().

data _null_;
  set tbl_names;
  call execute(cats('%st(',table_name,');'));
run;

But if your macro is complicated and uses macro variables it generates to control the logic of the macro then you will have a timing issue as CALL EXECUTE() will push the SAS statements the macro generates onto the stack to run.

If you add %NRSTR() then the macro call itself will be pushed onto the stack and timing issues will be avoided.

data _null_;
  set tbl_names;
  call execute(cats('%nrstr(%st)(',table_name,');'));
run;

 

Occasional Contributor
Posts: 5

Re: Run macro on a list of values or a dataset containing table names

Re: First why are you storing multiple dataset names in one observation instead of in many observations?  

 

I tried storing it in a single column and use it. But failed at running the macro step.

 

data _null_;
  set tbl_names;
  call execute(cats('%nrstr(%st)(',table_name,');'));
run;

 

Call execute worked. I was not aware of this. I am going to research more on this and nrstr.

 

Thank you for your quick response.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 80 views
  • 1 like
  • 2 in conversation