Determine the value of macro variable dynamically

[David_Billa]

I've the code as follows and it is working fine even if the value of the macro variables are swapped between them. In the below code even if you change the value between _OUTPUT1 and _OUTPUT0 it will display the right results. In this case the value IFRSITSS.META_DATA is fixed and it can be assigned either to _OUTPUT0 or _OUTPUT1. Other value IFRSITSS.INSURANCE varies.In either case, following code yields right results as given below.

Now the customer want to introduce two more macro varibles like %let _OUTPUT2=IFRSITSS.AUDIT_TRAIL;
and this value is fixed and it will never change. But this value can be assigned to any of the macro variables like _OUTPUT0 or _OUTPUT1 or _OUTPUT2. Other macro variable is
%let sql_audit_itss=AUDIT_TRAIL; and this value and the macro variable will never change.

In this case how can I tweak the below %if clause and createtwo more macro variables like audit_table and audit_table_sql.

Hint is %let sql_audit_itss=AUDIT_TRAIL; and %let sql_meta_itss=META_DATA; will never change and the values IFRSITSS.META_DATA and IFRSITSS.AUDIT_TRAIL is fixed and it will be assigned to any of the macro variables _OUTPUT0 or _OUTPUT1 or _OUTPUT2. Any help to tackle the Scenario using macro? Any ways to do this with other than %if.

Desired results are also given below. Program which I've is,

%let _OUTPUT1 = IFRSITSS.META_DATA;
%let _OUTPUT0 = IFRSITSS.INSURANCE;
%let sql_meta_itss=META_DATA;

data _null_;
%if %substr(_OUTPUT0.,10,%length(_OUTPUT0.))= sql_meta_itss. %then %do;
%let trans_table_sql=%substr(_OUTPUT1.,10,%length(_OUTPUT1.));
%let trans_table=_OUTPUT1.;
%let Meta_table_sql=%substr(_OUTPUT0.,10,%length(_OUTPUT0.));
%let Meta_table=_OUTPUT0.;
%end;

%if %substr(;_OUTPUT0.,10,%length(_OUTPUT0.)) NE sql_meta_itss. %then %do;
%let trans_table_sql=%substr(_OUTPUT0.,10,%length(_OUTPUT0.));
%let trans_table=&_OUTPUT0.;
%let Meta_table_sql=%substr(_OUTPUT1.,10,%length(_OUTPUT1.));
%let Meta_table=_OUTPUT1.;
%end;
run;

%put #### Trans_table_sql value is trans_table_sql.;
%put #### Trans_table value is trans_input.;
%put #### Meta_table_sql value is Meta_table_sql.
%put #### Meta_table value is Meta_table.;

Results:

Trans_table value is IFRSITSS.INSURANCE
Trans_table_sql value is INSURANCE
Meta_table value is IFRSITSS.META_DATA
Meta_table_sql value is META_DATA

Desired results for the Scenario which I explained above is,

Trans_table value is IFRSITSS.INSURANCE
Trans_table_sql value is INSURANCE
Meta_table value is IFRSITSS.META_DATA
Meta_table_sql value is META_DATA
Audit_table value is IFRSITSS.AUDIT_TRAIL
Audit_table_sql value is AUDIT_TRAIL

[Tom]

Why did you include these statements?

data _null_;
run;

If you are going to insert SAS statement that will force a new SAS step why not just write the logic using data step code instead of macro code?  That is normally a LOT easier to write, read and debug than macro code.

[David_Billa]

@Tom In real life it's very big macro code which includes other steps. I just want to update the logic only in the steps which I posted. I'm fine to receive help in data step:) Later I will adjust it to macro.

[Tom]

Did you accidently type your SAS code into and HTML file?

Why does it have & in it?

[David_Billa]

@Tom Corrected now

[Tom]

I cannot figure out what you are asking.

Please explain what is the input.  What is the desired output. What is are the rules for converting the input to the output?

Is there some pattern of text that appears in the values of OUTPUT0 (or OUTPUT1) that helps you figure out whether to use it as the META or the TRANS target macro variable?

Note we probably do not need to worry about creating the xxx_SQL macro variable of those look like they are simply the last word of the corresponding xxx macro variable.  For example once you have set TRANS_TABLE you can make TRANS_TABLE_SQL from it very easily.

%let trans_table_sql=%scan(&trans_table,-1,.);

[David_Billa]

@Tom This is the rule as I mentioned in my post.

I've the code as follows and it is working fine even if the value of the macro variables are swapped between them. In the below code even if you change the value between _OUTPUT1 and _OUTPUT0 it will display the right results. In this case the value IFRSITSS.META_DATA is fixed and it can be assigned either to _OUTPUT0 or _OUTPUT1. Other value IFRSITSS.INSURANCE varies.In either case, following code yields right results as given below.

Now the customer want to introduce two more macro varibles like %let _OUTPUT2=IFRSITSS.AUDIT_TRAIL;
and this value is fixed and it will never change. But this value can be assigned to any of the macro variables like _OUTPUT0 or _OUTPUT1 or _OUTPUT2. Other macro variable is
%let sql_audit_itss=AUDIT_TRAIL; and this value and the macro variable will never change.

[Tom · Accepted solution]

If the values are fixed then just hard code them. To find the odd man out just concatenate all three inputs and remove the "fixed" values. What's left is the third value.

%* Set example input values ;
%let _output0=IFRSITSS.META_DATA;
%let _output1=IFRSITSS.INSURANCE;
%let _output2=IFRSITSS.AUDIT_TRAIL;

%* Set the FIXED values ;
%let meta_table=IFRSITSS.META_DATA;
%let audit_table=IFRSITSS.AUDIT_TRAIL;

%* Calculate TRANS_TABLE by removing the FIXED table names from the list of all three ;
%let trans_table=&_output0 &_output1 &_output2;
%let trans_table=%sysfunc(tranwrd(&trans_table,&meta_table,));
%let trans_table=%sysfunc(tranwrd(&trans_table,&audit_table,));

Results:

300  %* Show the results ;
301  %put &=trans_table;
TRANS_TABLE=IFRSITSS.INSURANCE

[David_Billa]

@Tom Thank you. Your code is working fine as excepted. Can we also handle the same scenario using parsing function?