CODEWITHSUNDEEP
sqldatesas
SMW
SMW

Reputation: 502

PROC SQL: Delete rows by Date with format of e8601dt

Having some issues deleting rows from a data set. They need to be deleted by a date criteria, but the variable is in e8601dt. format. One thing I noticed about the variable is that its a number type variable, but left aligned (not sure if that has relevance or not), so I attempted to substring, and some additional attempts (below)...no success -

PROC SQL;
DELETE *
FROM DATASETS.BATCH_REPORT
WHERE datepart(BATCH_DATE) > '2015-10-01'
;
QUIT; 

PROC SQL;
DELETE *
FROM DATASETS.BATCH_REPORT
WHERE BATCH_DATE > '11oct2015'd
;
QUIT;

Assuming there has to be an easy way to call out a value in this format...or will I need to convert this variable to a more compliable format, then do my processing?

Upvotes: 0

Views: 587

Answers (2)

Beege
Beege

Reputation: 665

You're close! Date conversions are a pain between systems. The representation of the values depends on the environment configuration.

Within proc SQL, I think you have to specify oracle functions (not the SAS datepart) Looks like you've figured out that Oracle's 'DATE' datatype stores both date&time within the same value. The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight). SAS has 2 different date types: date and datetime.

I'd suggest using the oracle to_date() function to compare against a character date, i.e.

WHERE BATCH_DATE > to_date('2015-10-01','yyyy-mm-dd')

If desired, you could use the oracle to_char(BATCH_DATE,'mm-dd-yyyy') to cast the date variable to a text value and then compare on the text value. But you loose some of the comparison power.

....edited due to new info about ...ew... db2 ..... :-)

I'm way NOT a DB2 guy, but maybe something like this?

First, set the date as in: (the date passed to DB2 needs the double quotes):

 CALL SYMPUT('INT_DATE',"'"||PUT(sas_date,YYMMDDD10.)||"'");

Then use in the SQL as in:

PROC SQL ;

WHERE   BATCH_DATE >= &INT_DATE

https://communities.sas.com/t5/SAS-Procedures/DB2-Date9-format-To-SAS-Serial-Date/td-p/32436

Upvotes: 1

SMW
SMW

Reputation: 502

OK...did some research. Apparently (and some one please correct me if I am wrong)...to use the e8601dt. format, a date value needs to be multiplied by 86400, then you can apply the format. So.....dividing by 86400 brought me back to the SAS data as an integer. This did the trick :

PROC SQL;
DELETE *
FROM SETS
WHERE ID >= 20372
;
QUIT;

Upvotes: 1

Related Questions