NEED to Change below code in to sql

Reply
Occasional Contributor
Posts: 5

NEED to Change below code in to sql

Could you please some one help me how to convert below SAS code in to SQL code

data crmsaimler_f;

  merge ipexcl_f(keep=claimno in=abc) crmsaimall_f(in=def);

  by claimno;

  if def and not abc;

run;

Respected Advisor
Posts: 4,655

Re: NEED to Change below code in to sql

Try this:

proc sql;

create table crmsaimler_f as

select *

from crmsaimall

where claimno not in (select claimno from ipexcl);

quit;

 

PG


PG
Super Contributor
Posts: 307

Re: NEED to Change below code in to sql

I assume by SQL code you mean PROC SQL code?

A couple of different ways you could accomplish this via PROC SQL:

Method 1:

PROC SQL;

     create table crmsaimler_f as

     select def.claimno

     from crmsaimall_f as def

     WHERE NOT EXISTS

          ( select 1

          from ipexcl_f as abc

          where abc.claimno = def.claimno)

     order by 1

;

quit;

Method 2:

PROC SQL;

     create table crmsaimler_f as

     select def.claimno

     from crmsaimall_f as def

     WHERE def.claimno NOT IN

          ( select distinct abc.claimno

          from ipexcl_f as abc )

     order by 1

;

quit;

Ask a Question
Discussion stats
  • 2 replies
  • 176 views
  • 0 likes
  • 3 in conversation