Desktop productivity for business analysts and programmers

Simple Query distinct not working with date variable?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Simple Query distinct not working with date variable?

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

01Jan2015

01Jan2015

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

01Jan2015

01Jan2015

02Jan2015

03Jan2015

 

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

 

 


Accepted Solutions
Solution
‎10-03-2015 12:04 PM
Respected Advisor
Posts: 4,606

Re: Simple Query distinct not working with date variable?

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;

 

PG

View solution in original post


All Replies
Solution
‎10-03-2015 12:04 PM
Respected Advisor
Posts: 4,606

Re: Simple Query distinct not working with date variable?

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;

 

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 242 views
  • 0 likes
  • 2 in conversation