topic Re: Simple Query distinct not working with date variable? in SAS Enterprise Guide

Simple Query distinct not working with date variable?

SciFiGuy0 — Sat, 03 Oct 2015 03:10:38 GMT

I've got a dataset containing a date field named Submitted:

01Jan2015

02Jan2015

03Jan2015

I'm running a simple query builder which produces this code:

PROC SQL;

CREATE TABLE WORK.Stuff AS

SELECT DISTINCT t1.Submitted

FROM WORK.INPUTSET t1

ORDER BY t1.Submitted;

QUIT;

The output result is:

01Jan2015

02Jan2015

03Jan2015

Shouldn't there only be three records in the output file?

PGStats — Sat, 03 Oct 2015 03:28:20 GMT

If your date field is a SAS date, it may contain a fractional part, try using

CREATE TABLE WORK.Stuff AS
SELECT DISTINCT round(t1.Submitted) as Submitted format=date9.
FROM WORK.INPUTSET t1
ORDER BY Submitted;
QUIT;

if your date field is a SAS datetime, it may contain a time value, try using

CREATE TABLE WORK.Stuff AS
SELECT DISTINCT datepart(t1.Submitted) as Submitted format=date9.
FROM WORK.INPUTSET t1
ORDER BY Submitted;
QUIT;