Help using Base SAS procedures

Filtering a Proc SQL by an expression

Reply
N/A
Posts: 0

Filtering a Proc SQL by an expression

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.
Super Contributor
Posts: 578

Re: Filtering a Proc SQL by an expression

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;
Super User
Posts: 9,775

Re: Filtering a Proc SQL by an expression

[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
Ask a Question
Discussion stats
  • 2 replies
  • 113 views
  • 0 likes
  • 3 in conversation