topic Re: Deleted and take the difference in SAS Enterprise Guide

Deleted and take the difference

hk2013 — Fri, 29 Jun 2018 17:50:44 GMT

I have two data sets with IDs and Cust_id data_1 is my main data set its a snapshot of our customer list at the start of the year and i want to compare to data_2 which is updated monthly.

Data_2 might have new cust_id added over the time but I dont want to look at those.

Data_1 : data at start of year

ID Cust_id

1 111

1 222

1 333

1 444

2 555

2 666

2 777

2 888

3 999

3 1000

3 1001

3 1002

Data_2

ID Cust_id

1 111

1 222

1 1110

1 5786

2 555

2 8989

2 0986

3 999

Want:

Compare Data_1 to Data_2 and only keep cust_ids in data_2 that are also in Data_1 and than get the diff

New_Data_2

ID Cust_id difference

1 111 2

1 222 2

2 555 3

3 999 3

difference = count(data_1.Cust_id) -count(data_2.Cust_id)

Re: Deleted and take the difference

Kurt_Bremser — Fri, 29 Jun 2018 17:19:29 GMT

Are you sure you want sum() and not count()? Because I can't see where you'd get just 2 from those numbers.

Re: Deleted and take the difference

hk2013 — Fri, 29 Jun 2018 17:39:21 GMT

sorry yeah i meant count of the cust_id

Re: Deleted and take the difference

novinosrin — Fri, 29 Jun 2018 18:23:20 GMT

data one;
input ID      Cust_id;
cards;
1          111
1          222
1          333
1         444
2          555
2          666
2          777
2          888
3         999
3        1000
3        1001
3        1002
;
data two;
input ID      Cust_id;
cards;
1          111
1          222
1          1110
1         5786
2          555
2          8989
2          0986
3         999
;
proc sql;
create table want as
select a.*,b.count-count(a.cust_id) as difference
from two a, (select *,count(cust_id) as count from one group by id) b
where a.cust_id=b.cust_id and a.id=b.id
group by a.id;
quit;

Re: Deleted and take the difference

novinosrin — Fri, 29 Jun 2018 18:37:47 GMT

data one;
input ID      Cust_id;
cards;
1          111
1          222
1          333
1         444
2          555
2          666
2          777
2          888
3         999
3        1000
3        1001
3        1002
;
data two;
input ID      Cust_id;
cards;
1          111
1          222
1          1110
1         5786
2          555
2          8989
2          0986
3         999
;

data want;
if _n_=1 then do;
   dcl hash h(dataset:'two', multidata: 'y');
   h.definekey('id','cust_id');
    h.definedone();
end;
_c=0;
do _n=1 by 1 until(last.id);
set one;
by ID    Cust_id;
if h.check()=0 then  _c+1;
end;
do until(last.id);
set one;
by ID    Cust_id;
if h.check()=0 then do;count=_n-_c;output;end;
end;
drop _:;
run;

Re: Deleted and take the difference

PGStats — Sat, 30 Jun 2018 04:54:50 GMT

Can also be done this way with a left join:

proc sql;
select 
    one.*,
    sum(missing(two.cust_id)) as difference
from 
    one left join two on one.id=two.id and one.cust_id=two.cust_id
group by one.id
having not missing(two.cust_id);
quit;