Find an Id which can be in multiple tables

[Filipvdr]

Hello,

Im building a Stored Process and i have like an ID which can be in 4 tables.

If it is in table A, i want to get the data from table A.
If ti is in table B, i want to get the data from table B,
...

at the moment i'm doing the following but it takes a long time, not efficient enough


/* See which type of data it is - in which EDC table do i find it */

proc sql noprint; select count(*) into:NumberEdcThick from RF300L3.edc_thick where col_ins_id = "&sel_col_ins_id";quit;
%put &NumberEdcThick;

proc sql noprint;
select count(*) into:NumberEdcCd from RF300L3.edc_cd where col_ins_id = "&sel_col_ins_id";quit;
%put &NumberEdcCd;


proc sql noprint; select count(*) into:NumberEdcTxrF from RF300L3.edc_txrf where col_ins_id = "&sel_col_ins_id";quit;
%put &NumberEdcTxrF;




%macro getData;
%if &NumberEdcCd > 0 %then %do;
data out_table;
set RF300L3.edc_cd;
where col_ins_id = "&sel_col_ins_id";
CALL SYMPUT('TYPE', 'EDC_CD - Format');
run;
%end;
%else %if &NumberEdcThick > 0 %then %do;
data out_table;
set RF300L3.edc_thick;
where col_ins_id = "&sel_col_ins_id";
CALL SYMPUT('TYPE', 'THICK - Format');
run;
%end;
%else %if &NumberEdcTxrF > 0 %then %do;
data out_table;
set RF300L3.edc_txrf;
where col_ins_id = "&sel_col_ins_id";
CALL SYMPUT('TYPE', 'TXRF - Format');
run;
%end;
%else %do;
data _null_;
CALL SYMPUT('TYPE', 'General - Format');
run;
%extract_edc_full(pInProcess WaferId User_id State Slot ROW_ID ProcessRecipe ProcessPlan PmProcedure MeasRecipe
MeasKey MainTool LotId LimitsKey InspectionTool Facility DuploWaferId Datim CustomKey
ChecklistActivityId CSIM_TIMESTAMP COL_INS_ID, out_table);
%end;
%mend;
%getData;

[Ksharp]

It looks like you want to set macro variable value when dataset is non-empty.
You can use dictionary table ( dictionary.tables ) to get the number of obs for special tables.
Maybe will save your some time.


Ksharp

[GertNissen]

Consider adding an index on column "col_ins_id" on your 3 input tables.

In your example you will always be reading first 3 tables + 1 table, another approach would in worst case only read through max 3 tables. If you first query the most likely table, then the other 2 tables might not be used that often. Or you could change the order, so the smallest table is first. Perhaps adding a KEEP statement, when reading would improve the performance (depending on your data)

/* See which type of data it is - in which EDC table do i find it */

%macro getData;
%let type=;
data out_table;
set RF300L3.edc_cd end=theend;
where col_ins_id = "&sel_col_ins_id";
if theend then CALL SYMPUT('TYPE', 'EDC_CD - Format');
run;
%if "&type" ne "" %then %do;
data out_table;
set RF300L3.edc_thick end=theend;
where col_ins_id = "&sel_col_ins_id";
if theend then CALL SYMPUT('TYPE', 'THICK - Format');
run;
%end;
%else %if "&type" ne "" %then %do;
data out_table;
set RF300L3.edc_txrf end=theend;
where col_ins_id = "&sel_col_ins_id";
if theend then CALL SYMPUT('TYPE', 'TXRF - Format');
run;
%end;
%else %do;
data _null_;
CALL SYMPUT('TYPE', 'General - Format');
run;
%extract_edc_full(pInProcess WaferId User_id State Slot ROW_ID ProcessRecipe ProcessPlan PmProcedure MeasRecipe
MeasKey MainTool LotId LimitsKey InspectionTool Facility DuploWaferId Datim CustomKey
ChecklistActivityId CSIM_TIMESTAMP COL_INS_ID, out_table);
%end;
%mend;
%getData;