We help IT Professionals succeed at work.

SAS Parameter file creation - Please help

aruku
aruku asked
on
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!!
Comment
Watch Question

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
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;

Author

Commented:
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.
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;

Author

Commented:
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.
Database Marketing Consultant
Commented:
Here is a routine that will create macro variables named coefValu1, coefValu2... coefValu_n and locValu1, locValu2...locValu_n where _n is the number of unique expt_ids.  Each of your runs would then reference the macro variables where _n is the run.  

You run the macro only once before the first run.  The macro variables remain available for the duration of the SAS session.

At the very beginning of the run use these statements to assign that run's macro variables.
     
%let _run=1;
%let _coef&_run= &&_coefValu&_run;
%let _location&_run= &&_locValu&_run;

To see the values, use this.

%put _run= &_run;
%put _coef1= &_coef1;
%put _location1= &_location1;
data test;
  infile cards;
  input 
  Exptid $ Run_date $    parm_name $  parm_value : $15.;
cards;
1 12/07            coef                 1.645
1 12/07            location         %str('/sas/abc')
2 12/07            coef                 1.765 
2 12/07            location          %str('/sas/b')
;
run;

options mprint nosymbolgen nomlogic;

%macro createMacroValues;
	** Count the number of unique keys/runs. ;
	proc sql noprint;
		select max(exptid)
		into : _numRuns
		from test
		where catx('/',run_date,year(today()))=put(today(),mmddyy10.);
	quit;

	%put _numRuns= &_numRuns;

   %** Define the macro variable as global so they are available outside this macro. ;
	%do _i= 1 %to &_numRuns;
		%global	_coefValu&_i;
		%global	_locValu&_i;
	%end;

   %** Create a dataset of of todays data.  This Step can reference a Teradata table if a libname with ;
   %** the Teradata engine is used or you can use Proc SQL instead. ;
	data todaysData; set test;
	         if catx('/',run_date,year(today()))=put(today(),mmddyy10.);
	run;

   %** Loop thru the source data and and produce a pair of macro variables for each key/run. ;
	%do _i= 1 %to &_numRuns;
		%put _i= &_i;
		data base; set todaysData(where=(exptid="&_i"));
			if parm_name='coef' then call symput("_coefValu&_i",parm_value);
			else call symput("_locValu&_i",parm_value);
			put _all_;
		run;
	%end;
%mend;

%createMacroValues;

** List the user-created macro variables to verify their existance. ;
%put _user_;

Open in new window

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