topic Re: Run macro on a list of values or a dataset containing table names in SAS Enterprise Guide

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

r0oki3 — Mon, 06 Aug 2018 15:16:05 GMT

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.

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

Tom — Mon, 06 Aug 2018 15:58:33 GMT

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;

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

r0oki3 — Mon, 06 Aug 2018 16:07:00 GMT

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.