topic Re: delete statement in Proc SQL in SAS Procedures

delete statement in Proc SQL

galenhew — Tue, 03 Apr 2018 06:39:40 GMT

Hi how do I convert SQL delete statements into Proc SQL delete statements? I would like to convert the following sql to proc sql.

delete #campaigntrk_comb
from #campaigntrk_comb a, campaigndb.dbo.CampaignTrck_Ended b
where a.NBID = b.nbid
and A.trandate = b.Trandate
and a.campcode <> b.targetcampaign
go

I tried the following which does not work:

proc sql;
delete from campaigntrk_comb as a
where EXISTS
(select a.nbid, a.trandate, a.campcode
from camp.CampaignTrck_Ended as b
where a.NBID = b.nbid
and a.trandate = b.Trandate
and a.campcode NE b.targetcampaign);
quit;

and I did at left outer join to exclude data and the result is different. Thanks.

Re: delete statement in Proc SQL

rudfaden — Tue, 03 Apr 2018 11:12:06 GMT

Syntax seems to be fine. What error do you get?

Re: delete statement in Proc SQL

ballardw — Tue, 03 Apr 2018 16:25:29 GMT

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of a dataset, the actual results and the expected results. Data should be in the form of a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.