Duplicate Records

garretta — Tue, 18 Sep 2012 19:50:34 GMT

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

18767 1

18779 3

Re: Duplicate Records

Linlin — Tue, 18 Sep 2012 20:34:55 GMT

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.))

;

Re: Duplicate Records

garretta — Tue, 18 Sep 2012 20:42:33 GMT

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

topic Re: Duplicate Records in SAS Enterprise Guide

Duplicate Records

Re: Duplicate Records

Re: Duplicate Records