topic Re: Filtering a Proc SQL by an expression in SAS Procedures

Filtering a Proc SQL by an expression

deleted_user — Tue, 05 Apr 2011 05:24:14 GMT

Hi all,

I'm trying to run a proc sql, deleting records where one variable = another variable but getting unexpected results. Basically I return many records where variable1 does indeed equal variable 2.

Here's my code;

proc sql;
create table combined3 as
select t1.PolicyNumber,
t1.AnnualPremium,
t2.AnnualPremium as AnnualPremium2
from brendan.aug as t1
left join
brendan.july as t2
on t1.PolicyNumber = t2.PolicyNumber
where t1.AnnualPremium = t2.AnnualPremium;
quit;

If anyone can tell me what I'm doing wrong I'd be most appreciative.

Many thanks in advamnce.

Re: Filtering a Proc SQL by an expression

DBailey — Tue, 05 Apr 2011 11:45:21 GMT

presuming you mean to create a table (combined3) that only has records where the annual premium for a policy is different between brendan.aug and brendan.july, then:

proc sql;
create table combined3 as
select t1.PolicyNumber,
t1.AnnualPremium,
t2.AnnualPremium as AnnualPremium2
from brendan.aug as t1
left join
brendan.july as t2
on t1.PolicyNumber = t2.PolicyNumber
where
t1.AnnualPremium ne coalesce(t2.AnnualPremium,0);
quit;

Re: Filtering a Proc SQL by an expression

Ksharp — Wed, 06 Apr 2011 06:15:52 GMT

[pre]
data class;
set sashelp.class;
run;
data match;
sex='F';
run;
proc sql;
delete from class
where sex in (select sex from match);
quit;
[/pre]

Before doing this code,Recommend you to back up your origin dataset,
Because it will delete rows in the origin dataset.

Ksharp