Date: Mon, 12 Jun 2006 05:00:10 GMT
Reply-To: Doug and Olga Eyre <olga-and-doug@NC.RR.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Doug and Olga Eyre <olga-and-doug@NC.RR.COM>
Organization: Road Runner High Speed Online http://www.rr.com
Subject: Re: Removing duplicates in Proc SQL SAS
Dean:
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
;
proc sql;
select distinct * from
(select client,
(not (imports = 0)) as importers,
(not (exports = 0)) as exporters,
(not (imports = 0 or exports = 0)) as combined
from a);
* output *
client importers exporters combined
123473 1 1 1
123474 1 1 1
123475 1 1 1
123476 1 1 1
212142 1 1 1
212152 1 1 1
212162 0 1 0
433422 1 1 1
520055 1 1 1
606688 1 1 1
693321 1 0 0
779954 1 1 1
866587 1 0 0
953220 1 1 1
953221 0 1 0
Erico
"That's right, I'm a math person, I'm not one of the 4 out of 3 people who
have trouble with fractions"
"Dean Edwards" <dean@DREAMNET.CO.NZ> wrote in message
news:001101c68d15$d157a710$0af3ea3c@deanl7zxt4dwzp...
> 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
