Solved

PROC SQL: Delete rows in which two date fields differ by more than 250 days

Posted on 2009-05-18
6
763 Views
Last Modified: 2012-05-07
Hi all, I;m not familiar with PROC SQL (I think its similar to MS SQL)

I am running a statistics program called SAS and am trying to delete certain rows in a database. The fields 'Date_Announced' and 'Date' are the ones I am concerned with. I'm trying to subtract the two coloumns and then delete the rows for which the result is >250 . The sheet is called Asx.mergernew.

Can someone help me with the exact command to type into my command window?
0
Comment
Question by:hubfub
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24409778
you can do something like:

delete from YourTableName where DATEDIFF(dd,Date_Announced,[date])>=205
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24409789
if you want in stored procedure than use this one.




create proc DeleteRecord
as
begin
delete from YourTableName where DATEDIFF(dd,Date_Announced,[date])>=205
end
 
 
--run abov proc
exec deleteRecords

Open in new window

0
 
LVL 1

Author Comment

by:hubfub
ID: 24409805
HI RiteshShah

DATEDIFF is not a command that is recognised nor is it a field. Is there a way to get around this? One field in ours is Date and the other is Date_Announced. What is dd?
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24409811
DATEDIFF is a date function built in in SQL Server, "DD" gives you difference in days.

can't you even run below command?



select DATEDIFF(dd,getdate()-250,getdate())

Open in new window

0
 
LVL 1

Author Comment

by:hubfub
ID: 24409837
When I run that command it says 'syntax error'

I am trying to run these commands in a statistics program called SAS although the command window says PROC SQL - is this why the commands are not running?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24409985
those command should run in SQL Server, not familiar with PROC SQL.
0

Featured Post

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question