DATA Step, Macro, Functions and more

Looking for exact and partial matches

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Looking for exact and partial matches

Hello

I have been using SAS for years but I am a basic programmer and generally keep it simple but I want to get better. I have this situation that I can use the following code but I would like it to be smarter. In this scenario the max n is 3 but it could be as many as 50 and I don't want to write out the code.  So if anyone out there can educate/enlighten me I would be very grateful

 

DATA checking;
INPUT CUSTOMER_ACCOUNT_ID n sum_accounts ;
CARDS;
260 1 10436965
260 2 10436965
9629 1 9629
9629 2 88532203
15191 1 15191
15191 2 15191
15191 3 15191
17712 1 17712
17712 2 43811252
17712 3 17712
;
run;

proc transpose data = checking out = x_checking (drop = _name_) prefix = count_;
by customer_account_id ;
id n;
var sum_accounts;
run;

data x_checking_1;
set x_checking;

format match $10.;
if count_3 eq . then
do;
if count_1 = count_2 then match = 'yes';
else match = 'no';
end;

else
do;
if count_1 = count_2 = count_3 then match = 'yes';
else if count_1 = count_2 or count_1 = count_3 or count_2 = count_3 then match = 'partial';
else match = 'no';

end;
run;

 

Cheers


Accepted Solutions
Solution
‎10-19-2015 10:27 PM
Respected Advisor
Posts: 3,887

Re: Looking for exact and partial matches

One way to go:

proc sql;
  create table want as
    select 
      CUSTOMER_ACCOUNT_ID, 
      count(*) as n_rows_per_CA, 
      count(distinct sum_accounts) as n_dif_vals_per_CA,
      case
        when calculated n_dif_vals_per_CA = 1 then 'yes'
        when calculated n_rows_per_CA = calculated n_dif_vals_per_CA then 'no'
        else 'partial'
        end as match length=7
    from checking
    group by CUSTOMER_ACCOUNT_ID
  ;
quit;

View solution in original post


All Replies
Solution
‎10-19-2015 10:27 PM
Respected Advisor
Posts: 3,887

Re: Looking for exact and partial matches

One way to go:

proc sql;
  create table want as
    select 
      CUSTOMER_ACCOUNT_ID, 
      count(*) as n_rows_per_CA, 
      count(distinct sum_accounts) as n_dif_vals_per_CA,
      case
        when calculated n_dif_vals_per_CA = 1 then 'yes'
        when calculated n_rows_per_CA = calculated n_dif_vals_per_CA then 'no'
        else 'partial'
        end as match length=7
    from checking
    group by CUSTOMER_ACCOUNT_ID
  ;
quit;
Occasional Contributor
Posts: 5

Re: Looking for exact and partial matches

That is great thanks - simple and effective. Thanks very much
Respected Advisor
Posts: 4,641

Re: Looking for exact and partial matches

[ Edited ]

My take on this...

 

DATA checking;
INPUT ID n sum_a ;
CARDS;
260 1 10436965
260 2 10436965
9629 1 9629
9629 2 88532203
15191 1 15191
15191 2 15191
15191 3 15191
17712 1 17712
17712 2 43811252
17712 3 17712
;

proc sql;
create table matches as
select ID, 
    case
        when max(count) >= 2 and max(count) = sum(count) then "yes"
        when max(count) >= 2 then "partial"
        else "no"
        end as match
from (
    select ID, sum_a, count(sum_a) as count
    from checking
    group by ID, sum_a)
group by ID;
select * from matches;
quit;
PG
Occasional Contributor
Posts: 5

Re: Looking for exact and partial matches

Thanks a million for taking time to respond. Cheers
Super User
Posts: 5,071

Re: Looking for exact and partial matches

A possibility:

 

proc sort data=checking;

   by customer_account sum_accounts;

run;

 

data checking;

   length match $ 10;

   match='no';

   do until (last.customer_account);

      set checking;

      by customer_account sum_accounts;

      if first.customer_account then first_amount = sum_accounts;

      if first.sum_accounts=0 then match='partial';

      if last.customer_account and sum_accounts=first_amount then match='yes';

   end;

   do until (last.customer_account);

      set checking;

      by customer_account;

      output;

   end;

   drop first_amount;

run;

 

Then run the same PROC TRANSPOSE, adding in MATCH.

Occasional Contributor
Posts: 5

Re: Looking for exact and partial matches

This is great - Thank you so much. I liked the Sql soln and it did exactly what I wanted but I want to learn an equivalent SAS soln and here it is. Thanks again.
Respected Advisor
Posts: 3,124

Re: Looking for exact and partial matches

Here is a data step solution:

proc sort data=checking out=_ck;
	by CUSTOMER_ACCOUNT_id sum_accounts;
run;

data want;
	do _n_=1 by 1 until (last.CUSTOMER_ACCOUNT_ID);
		set _ck;
		by CUSTOMER_ACCOUNT_ID sum_accounts;
		_ct+first.sum_accounts;
	end;

	length match $ 7;

	if _ct = _n_ and _ct>1 then
		match='No';
	else if _ct < _n_ and _ct >1 then
		match ='Partial';
	else if _ct = 1 and _n_ >1 then
		match = 'Yes';
	else match ='N/A';
	_ct=.;
	keep CUSTOMER_ACCOUNT_ID match;
run;
Occasional Contributor
Posts: 5

Re: Looking for exact and partial matches

Thanks very much
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 280 views
  • 3 likes
  • 5 in conversation