topic how to find duplicated observations by amount in SAS Programming

how to find duplicated observations by amount

tulip — Fri, 18 Dec 2009 00:32:30 GMT

Hi, Pros:

Here is my senario:

Data structure as follows:
*******************************************
id date service type amt
1 12/1/2009 station1 A +5
1 12/1/2009 station1 A +15
1 12/1/2009 station1 A -15

2 12/1/2009 station1 B +5
2 12/1/2009 station1 B -5
2 12/1/2009 station1 B +25
2 12/1/2009 station1 B -25
2 12/1/2009 station1 B 20
*****************************************

Identify unique observation by sort by id date service type;

The following result is expected. Many thanks. Tulip
***************************************
id date service type amt
1 12/1/2009 station1 A +5
2 12/1/2009 station1 B 20
***************************************

Re: how to find duplicated observations by amount

sbb — Fri, 18 Dec 2009 03:38:17 GMT

Please provide specific expectation so one will not make their own interpretation. Specifically, I see where you chose the "first" occurence of variables "id date service type" with the first grouping but you chose the "last" occurences of the 2nd group.

For this objective, regardless, you should explore using PROC SORT and a DATA step approach using a BY statement and IF FIRST. and/or IF LAST. to perform your data selection.

Suggested Google advanced search argument for related DOC:

data step by group processing site:sas.com

Scott Barry
SBBWorks, Inc.

Re: how to find duplicated observations by amount

deleted_user — Fri, 18 Dec 2009 07:19:20 GMT

Hi tulip

I'm not sure if I understand the problem exactly, however this code perform expected results.

DATA station;
INPUT id date:$10. service $ type $ amt;
CARDS;
1 12/1/2009 station1 A +5
1 12/1/2009 station1 A +15
1 12/1/2009 station1 A -15
2 12/1/2009 station1 B +5
2 12/1/2009 station1 B -5
2 12/1/2009 station1 B +25
2 12/1/2009 station1 B -25
2 12/1/2009 station1 B 20
;
RUN;

proc sql;
select id, date, service, type, amt
from station
group by id, date, service, type, abs(amt)
having count(*) < 2
;
quit;

best regards

Tomaž

Re: how to find duplicated observations by amount

tulip — Fri, 18 Dec 2009 15:52:34 GMT

Hi, guys:

It is so nice of you. I appreciate your help very much.

The issue in this case can not be taken care of by using sort and by first....

The final observation needs to contain the correct amount.

Thanks Tom. Could you please try again the following case please?

DATA station;
INPUT id date:$10. service $ type $ amt;
CARDS;
1 12/1/2009 station1 A +5
1 12/1/2009 station1 A +15
1 12/1/2009 station1 A -15

2 12/1/2009 station1 B +5
2 12/1/2009 station1 B -5

2 12/1/2009 station1 B 20
2 12/1/2009 station1 B +25
2 12/1/2009 station1 B -25

3 12/1/2009 station1 B +55
3 12/1/2009 station1 B -55
3 12/1/2009 station1 B 55
;
RUN;

proc sql;
select id, date, service, type, amt
from station
group by id, date, service, type, abs(amt)
having count(*) < 2
;
quit;

final expects the following.
1 12/1/2009 station1 A +5

2 12/1/2009 station1 B 20

3 12/1/2009 station1 B 55 Message was edited by: tulip

Re: how to find duplicated observations by amount

tulip — Fri, 18 Dec 2009 16:02:18 GMT

Hi, Tomaz:

Could you please try again the following case additionally please?
there is length limit so I do not show all.
DATA station;
INPUT id date:$10. service $ type $ amt;
CARDS;
3 12/1/2009 station1 B +55
3 12/1/2009 station1 B -55
3 12/1/2009 station1 B 55
;
RUN;

proc sql;
select id, date, service, type, amt
from station
group by id, date, service, type, abs(amt)
having count(*) < 2
;
quit;

final expects the following.
1 12/1/2009 station1 A +5

2 12/1/2009 station1 B 20

3 12/1/2009 station1 B 55

Re: how to find duplicated observations by amount

tulip — Fri, 18 Dec 2009 16:03:16 GMT

final show is 3 12/1/2009 station1 B +55

Re: how to find duplicated observations by amount

deleted_user — Mon, 21 Dec 2009 07:36:00 GMT

I see
maybe using sum instead abs is more appropriate.

Tomaž

Re: how to find duplicated observations by amount

tulip — Sat, 23 Jan 2010 15:26:59 GMT

Belated thanks, Tomaz. Further help is needed.

when I replace abs to sum function. Error message came up. Please advise. Thanks.

DATA station;
INPUT id date:$10. service $ type $ amt;
CARDS;
3 12/1/2009 station1 B +55
3 12/1/2009 station1 B -55
3 12/1/2009 station1 B +55
;
RUN;
proc sql;
select id, date, service, type, amt
from station
group by id, date, service, type, abs(amt)
having count(*) < 2 ;
;
quit; Message was edited by: tulip

Re: how to find duplicated observations by amount

sbb — Sat, 23 Jan 2010 16:59:07 GMT

As I stated previously, you can use IF LAST. THEN OUTPUT; with a sorted file using a BY statement and being the last variable in the BY. From your explanation, I believe the BY variables are ID, DATE (suggest inputting as a SAS NUMERIC date, not character), SERVICE and TYPE. Use EQUALS on the SORT to ensure the incoming order is maintained for your AMT values.

Again, see the recommended SAS support site DOC references for details, and also the EQUALS parameter discussion for PROC SORT.

Scott Barry
SBBWorks, Inc.