Hi,
I have a table where I need to delete rows based on two fields, one of which is a date field in YYMMDDN8. format. The idea is to delete rows where the date is not 20170102. I tried a simple PROC SQL command to delete these rows (the date field is marked as 'y' here):
proc sql;
delete
from work.table
where x='12345678' and y ne 20170102;
quit;
What happens is the command deletes all of the rows where x='12345678' because the date field is not in the right format. How do I need to change the code so that it works?
Use a date literal, or input text to a SAS date numeric.
proc sql; delete from work.table where x='12345678' and y ne "02JAN2017"d; quit;
Note the quotes and the d, this way only accepts ddmmmyyyy format.
The other:
proc sql; delete from work.table where x='12345678' and y ne input('20170102',yymmdd8.); quit;
Use a date literal, or input text to a SAS date numeric.
proc sql; delete from work.table where x='12345678' and y ne "02JAN2017"d; quit;
Note the quotes and the d, this way only accepts ddmmmyyyy format.
The other:
proc sql; delete from work.table where x='12345678' and y ne input('20170102',yymmdd8.); quit;
Thank you, that worked perfectly! I had tried something similar to the first option, but with single quotation marks.
@tv80 wrote:
Thank you, that worked perfectly! I had tried something similar to the first option, but with single quotation marks.
Single quotes should only have an impact if you are also using macro variables OR if you had spaces between any of the digits and the month name.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.