Desktop productivity for business analysts and programmers

Duplicate Records

Reply
Occasional Contributor
Posts: 5

Duplicate Records

The following query returns a count of events by day.  event_posting_date sample value: 05/03/11

If I run the query in SQL Server, I get the proper number of records for each day, but when I run the query in SAS EG using proc sql, I get duplicates.  Specifically, if the count field returns 1, I get one row, if the count field returns 2, I get 2 identical rows, if the count field returns 3, I get 3 identical rows.

I can throw a distinct on the query to eliminate this, but I'd rather understand why this is happening and address properly.

Thanks

select

input(event_posting_date,MMDDYY8.)   as day,

count(cybs_exception_id)                     as count

from frs.staging_records

group by

input(event_posting_date,MMDDYY8.)

;

day          count

18570     1

18764     2

18764     2

18767     1

18779     3

18779     3

18779     3

Super Contributor
Posts: 1,636

Re: Duplicate Records

is event_posting_date in datetime format?

try:

select

int(input(event_posting_date,MMDDYY8.))   as day,

count(cybs_exception_id)                     as count

from frs.staging_records

group by

int(input(event_posting_date,MMDDYY8.))

;

Occasional Contributor
Posts: 5

Re: Duplicate Records

event_posting_date is a varchar(28) in the following format:  05/03/11 (MM/DD/YY)

Ask a Question
Discussion stats
  • 2 replies
  • 192 views
  • 0 likes
  • 2 in conversation