SAS Macro Proc SQL w/ Multiple Conditions

Question

I'm trying to run a proc sql macro that contains multiple conditions strung together. Is there a way to correct the code below for the 2nd macro (rn_cnt) or is there a better approach? Background: There are 4 datasets: (quotes, orders, invoices, contracts). The IDs highlighted in the code do not exist in 1 of the datasets (quotes) and so I would like to exclude them from counts. In other words "For these counts, do not count these IDs as missing if querying from Quotes." How would I add this additional condition to the proc sql where statement of excluding IDs only when Quotes is queried? Example IDs I would want to count are all other IDs, such as: 097, 098, 099, 106, 107, etc. These IDs all exist across the other 3 datasets. Thank you.

%macro importdt (dataname, source);
proc sql;
        create table &dataname as select *
        from &source;
quit;
%mend importdt;

%importdt(quotes, sales.quotesall);
%importdt(orders, ordersall);
%importdt(invoices, invoicesall);
%importdt(contracts, contractsall);


%macro rn_cnt (misscust, data1, data2, &dataname);
proc sql;
        create table &misscust as select count(distinct cust_id) as Misscnt from &data1
        where cust_id not in (select distinct cust_id from &data2) and cust_id not in (('101' '102' 103' '104' '105') when &dataname = quotes);
quit;
%mend rn_cnt;


%rn_cnt(quotes_orders, quotes, orders);
%rn_cnt(quotes_invoices, quotes, invoices);
%rn_cnt(quotes_contracts, quotes, contracts);
%rn_cnt(orders_invoices, orders, invoices);
%rn_cnt(orders_contracts, orders, contracts);
%rn_cnt(invoices_contracts, invoices, contracts);

Answer 1

Why not just make a single dataset and then have the SQL code INSERT new observations so that at the end of your calls you have all of the counts in one dataset.

I cannot figure out what the DATANAME parameter is for as the SQL code that references makes no sense. But if you fix that them make a variable to hold the value of that also.

data misscount;
  length ds1 ds2 $32 misscnt 8;
  stop;
run;


%macro rn_cnt (data1, data2, dataname);
proc sql;
insert into misscount
  select "&data1","&data2",count(distinct cust_id) as Misscnt
  from &data1
  where cust_id not in (select distinct cust_id from &data2)
    and cust_id not in (('101' '102' '103' '104' '105')
/* What the heck is this??  when &dataname = quotes */
    )
;
quit;
%mend rn_cnt;

If the goal of that last parameter is to control whether or not to exclude that list of hard coded ids then perhaps you could do something like:

%macro rn_cnt (data1, data2, quotes);
proc sql;
insert into misscount
  select "&data1","&data2",count(distinct cust_id) as Misscnt
  from &data1
  where cust_id not in (select distinct cust_id from &data2)
%if %qupcase(&quotes)=Y %then %do;
    and cust_id not in ('101' '102' '103' '104' '105')
%end;
;
quit;
%mend rn_cnt;

And then call it like:

%rn_cnt(quotes, orders, quotes=Y);

or

%rn_cnt(quotes, orders, quotes=N);

to get two different results.

And if the goal is to change the code when the second parameter is literally the string quotes then you don't need the third parameter at all.

%if %qupcase(&data2)=QUOTES %then %do;
    and cust_id not in ('101' '102' '103' '104' '105')
%end;