asked on

SAS Parameter file creation - Please help

Hi all,

In our current process. We are planning to implement the parameter file in such a way to pull the records directly from a teradata table and use that as parameter file.

For example:

Table A (Permenent table in Teradata)

Expt id Run_date param_name param_value

1 12/06 coef 1.645
1 12/06 location %str('/sas/abc')
2 12/06 coef 1.765
2 12/06 location %str('/sas/b')

I need to query this table based on the run date and pull the information of the expt_id's and run the whole SAS code twice because there are two experiments.

How can I resolve the above to

%let coef = 1.645;
%let coef = %str('/sas/b');

or anyway where these are the macro variables

Appreciate your help!!

Aloysius Low

after reading the table (or while reading the table, you could use the call execute command (within a data step) to generate sas codes to execute while on the fly based on the value read...

for eg

data _null_;
set tableA;
where run_date=today();
call execute("data x;");
if (param_name="coef") then do;
call execute("coef=" || param_value);
end;
else do;
call execute("location="|| param_value);
end;
run;

this step will create a table called X with the coef and location values as filtered from the teradata table

Aloysius Low

after clicking on the submit button i realised i forgot to include a run statement in the call execute"

data _null_;
set tableA;
where run_date=today();
call execute("data x;");
if (param_name="coef") then do;
call execute("coef=" || param_value);
end;
else do;
call execute("location="|| param_value);
end;
call execute("run");
run;

aruku

ASKER

Thanks for the response lowaloysius.....But how can i differenciate the parameters based on the expt id because the code should run for an expt id and then run for the next expt id.

Aloysius Low

you could add a by variable in the outer data _null_ step, and use first.expt_id and last.expt_id to control the generation of the data x and run statements:

data _null_;
set tableA;
where run_date=today();
by expt_id;
if first.expt_id then do;
call execute("data x;");
end;
else do;
if (param_name="coef") then do;
call execute("coef=" || param_value||";");
end;
else do;
call execute("location="|| param_value||";");
end;
end;
if (last.expt_id) then do;
call execute("run;");
end;
run;

aruku

ASKER

Thanks.

Can I use the same code if there are more then 2 expt_id's as well or not, because there are lot of scenarios where we may have around 6 expt_id's running on a single day. please let me know.

ASKER CERTIFIED SOLUTION

d507201

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aloysius Low

yes you can use for more than 2 expt_id... by virtue of the by statement, the step will be repeated for as many expt_id as there are in your parameter table