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

import? Looks more like you are downloading from some external database. — Tom
– Tom, Commented May 1, 2024 at 3:14
This when &dataname = quotes is not SQL syntax. What do you mean by that? — Tom
– Tom, Commented May 1, 2024 at 3:22
@Tom - Thank you Tom - makes sense and much more efficient! As for our remaining issue with the 'when &dataname = quotes,' I think I should have noted that as maybe 'when &data2 = quotes.' What I'm trying to do is to exclude those IDs from counts when the Quotes dataset is being referenced. This is because those IDs had all their records deleted from the Quotes file in the past accidentally, so for now I don't want those counts included in my missing counts. How would I add this caveat/ exclusion condition to the where statement? — Jont
– Jont, Commented May 1, 2024 at 11:48
You need to explain in a different way. What is QUOTES? Another dataset? So are you saying that if this CUST_ID is in QUOTES then it should not count, just like you aren't counting CUST_ID='101'? Update the question to include actual example CUST_ID values for all three datasets that demonstrate which ones you want to count. — Tom
– Tom, Commented May 1, 2024 at 13:37
@Tom - I just added details and a clearer explanation to the question. Yes, I would want to exclude 101 and the other IDs only when the QUOTES dataset is being queried. I have added sample customer IDs to count, which exist across the other 3 datasets. — Jont
– Jont, Commented May 1, 2024 at 14:06

Tom · Accepted Answer · 2024-05-01 19:03:47Z

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;

Collectives™ on Stack Overflow

SAS Macro Proc SQL w/ Multiple Conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related