BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
carmo
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

8 REPLIES 8
Patrick
Opal | Level 21

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;
carmo
Fluorite | Level 6
That is great thanks - simple and effective. Thanks very much
PGStats
Opal | Level 21

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
carmo
Fluorite | Level 6
Thanks a million for taking time to respond. Cheers
Astounding
PROC Star

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.

carmo
Fluorite | Level 6
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.
Haikuo
Onyx | Level 15

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;
carmo
Fluorite | Level 6
Thanks very much

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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