Date: Sun, 11 Jun 2006 17:13:22 +1200
Reply-To: Dean Edwards <dean@DREAMNET.CO.NZ>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Dean Edwards <dean@DREAMNET.CO.NZ>
Subject: Removing duplicates in Proc SQL SAS
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
reply-type=original
Is there anyway to remove the duplicates in prco SQL SAS so that I can count
it once?
data a;
input area $ client imports exports combine;
cards;
Dairy 123473 300 8457 132230
Dairy 123474 400 11353 135227
Dairy 123475 700 14248 138423
Dairy 123476 500 17144 141120
Forestry 123473 100 20039 143614
Forestry 123473 200 22935 146611
Forestry 212142 300 25830 238252
Forestry 212152 400 343255 777098
Forestry 212162 0 4436 351225
Forestry 433422 4000 643634 1081056
Forestry 520055 700 46 520801
Forestry 606688 800 643364 1250852
Forestry 693321 900 0 694221
Forestry 779954 4321 643 784918
Forestry 866587 5211 0 871798
Forestry 953220 6222 643 960085
Forestry 953221 0 46853 960085
run;
proc sql;
create table test as
select client,
(not (imports = 0)) as client_importers,
(not (exports = 0)) as client_exporters,
(not (imports = 0 or exports = 0)) as combined,
count(client) as number
from a
group by
client
order by
client
;
quit;
proc print;
run;
client_ client_
Obs importers exporters combined number
1 1 1 1 3
2 1 1 1 3
3 1 1 1 3
4 1 1 1 1
5 1 1 1 1
6 1 1 1 1
7 1 1 1 1
8 1 1 1 1
9 0 1 0 1
client_ client_
Obs importers exporters combined number
10 1 1 1 1
11 1 1 1 1
12 1 1 1 1
13 1 0 0 1
14 1 1 1 1
15 1 0 0 1
16 1 1 1 1
17 0 1 0 1
so I get something like this
client_ client_
Obs importers exporters combined number
1 1 1 1 1
2 1 1 1 1
3 1 1 1 1
4 1 1 1 1
5 1 1 1 1
6 1 1 1 1
7 0 1 0 1
client_ client_
Obs importers exporters combined number
8 1 1 1 1
9 1 1 1 1
10 1 1 1 1
11 1 0 0 1
12 1 1 1 1
13 1 0 0 1
14 1 1 1 1
15 0 1 0 1
when I have this statement below:
having count(*)= 1
group by
client
having count(*)= 1
order by
client
;
quit;
client_ client_
Obs client importers exporters combined number
1 123474 1 1 1 1
2 123475 1 1 1 1
3 123476 1 1 1 1
4 212142 1 1 1 1
5 212152 1 1 1 1
6 212162 0 1 0 1
7 433422 1 1 1 1
8 520055 1 1 1 1
9 606688 1 1 1 1
10 693321 1 0 0 1
11 779954 1 1 1 1
12 866587 1 0 0 1
13 953220 1 1 1 1
14 953221 0 1 0 1
I actually want to count one of these duplicates
so I get one count of client code 123473
client_ client_
Obs client importers exporters combined number
1 123473 1 1 1 1
