How do I run macros with variables from external file

[alazer]

Hi,

I'm trying to write some code that will do the following three steps:

1. Write a macro that take a bunch of different inputs (say, 10+ different variables), and calculate an expense using those variables
2. Import an Excel file that has several hundred different permutations of the 10+ variables
3. Run the macro for all permutaions in the imported file

I can write steps 1 and 2, but am not sure how to do #3 (short of manually calling the macro several hundred times).  Does anyone have any experience with something like this?  Any help would be appreciated.

I am using Enterprise Guide, but I also have 9.2.

Thanks.

[PGStats]

What do Steps 1 and 2 look like?

[ballardw]

As @PGStats says, some details of what steps 1 and 2 actually look like would be helpful.

Without knowing what your current macro looks like you might be able to use the data from the Excel permutations (you do have that as a SAS dataset don't you?) as a control file using a data _null_ step and call execute to either do the calculations or to call your macro.

[alazer]

A simplified version of my macro is below.  I have a list of costs attributed to several people over the course of the year, and want to calculate the costs by each person if they were responsible for different amounts for each category (basically costs for insurance with different copay amounts).

Macro Calc_Costs (T1, T2, T3);

%if cat = 'T1' THEN copay=&T1.;

%if cat = 'T2' THEN copay=&T2.;

%if cat = 'T3' THEN copay=&T3.;

%MEND CALC_COSTS;

I would then want to run this for several permutation of those 3 variables, without having to call it all those times. 

Thanks.

[alazer]

I realize that it's probably not the most elegant solution, but I did come up with a work around for my issue, so thank you to PGStats annd ballardw for looking at my question.

Since my second post was probably not as clear as it could have been, I'll clarify as well.  Basically, I had something similar to the below macro, which calculated costs depending on whether a particular data point belonged to a particular category.

%macro cost_in_category (cat_1, cat_2, cat_3);

data table;

set othertable;

  if category=1 then cost=&cat_1.;

  if category=2 then cost=&cat_2.;

  if category=3 then cost=&cat_3.;

run;

%MEND;

I have several permutations of cat_1,2,3, so rather than doing

%cost_in_category(1,2,3);

%cost_in_category(1,2,4);

%cost_in_category(2,3,5);

etc.

I wanted some code to automatically calculate these for me.  I would import the permutations from a table that looked something like:

n   cat_1   cat_2   cat_3

1   1          2          3

2   1          2          4

3   2          3          5

My solution was to create an extra step in my macro, and restrict my permutation table to record i.

%macro cost_in_category ;

%do i = 1 %to <number of records in table>;

data costcat_&i.;

SET permutations_table;

WHERE n = &i.

run;

PROC SQL;

create table base_data_with_costcats_&i. as

SELECT a.*, b.*

FROM base_data a, costcat_&i b;

QUIT;

data table;

set base_data_with_costcats_&i;

  if category=1 then cost=cat_1;

  if category=2 then cost=cat_2;

  if category=3 then cost=cat_3;

run;

%END;

%MEND;