topic Re: Removing observations during create table step in SAS Procedures

Removing observations during create table step

MillerEL — Tue, 12 Jun 2018 19:42:22 GMT

Hello,

I have the following code below which uses 2 files to identify which observations are new and which are old by setting New=0 when the ID number is found in both files. Is it possible to delete all observations where New=0 in this procedure? I am trying to simplify my code, so I don't want to do it in a separate data step but I can't find syntax that works.

Thanks!

-EM

    PROC SQL;
        CREATE TABLE work.newobs as select *, 
	    case
            WHEN IDnumber=IDnum then 0 /*old obs*/
            ELSE 1 end as New /*new obs*/
            FROM newYTD_&FileDate right join old_&FileDate
                on dc_number=cnumb;
    /*can we delete New=0 in this step?*/
    quit;

Re: Removing observations during create table step

ballardw — Tue, 12 Jun 2018 19:50:33 GMT

perhaps

PROC SQL;
        CREATE TABLE work.newobs as select *, 
	    case
            WHEN IDnumber=IDnum then 0 /*old obs*/
            ELSE 1 end as New /*new obs*/
            FROM newYTD_&FileDate right join old_&FileDate
                on dc_number=cnumb
         where calculated new=1
    ;
    quit;

Re: Removing observations during create table step

PGStats — Tue, 12 Jun 2018 19:52:35 GMT

It is not clear which column comes from which table in your query. You could use something like:

PROC SQL;
CREATE TABLE work.newobs as 
select * 
FROM newYTD_&FileDate 
where IDnum not in (select IDnumber from old_&FileDate);
quit;

Re: Removing observations during create table step

MillerEL — Tue, 12 Jun 2018 19:52:44 GMT

returned only one blank observation...

Re: Removing observations during create table step

SuryaKiran — Tue, 12 Jun 2018 20:50:37 GMT

PROC SQL;
        CREATE TABLE work.newobs as select *, 
	    case
            WHEN IDnumber=IDnum then 0 /*old obs*/
            ELSE 1 end as New /*new obs*/
            FROM newYTD_&FileDate right join old_&FileDate
                on dc_number=cnumb
         where IDnumber<>IDnum
; 
quit;

Use the same condition you used for the CASE expression in the where clause.

I was wondering why @ballardw solution didn't work. Do you have same data types or sometimes leading and trailing blanks maybe and issue.

Re: Removing observations during create table step

MillerEL — Tue, 12 Jun 2018 20:52:41 GMT

This worked great! Thank you!

Re: Removing observations during create table step

Tom — Tue, 12 Jun 2018 21:55:13 GMT

Why would you want to do that using SQL code instead of DATA step?

data newobs  ;
  merge 
    old_&FileDate(in=inold rename=(cnumb=dc_number))
    newYTD_&FileDate(in=innew)
  ;
  by dc_number ;
  if inold and innew ;
run;

Re: Removing observations during create table step

MillerEL — Thu, 19 Jul 2018 17:53:46 GMT

I just found out that the variable I am using to select records, IDNum, is only unique by year. So I need to select cases based on unique instances of year and IDNum. I tried the following but it didn't work. Any suggestions?

PROC SQL;
CREATE TABLE work.newobs as 
select * 
FROM newYTD_&FileDate 
where (IDnum not in (select IDnumber from old_&FileDate) and year not in (select year from Old&FileDate));
quit;

Re: Removing observations during create table step

PGStats — Thu, 19 Jul 2018 18:03:59 GMT

Try

PROC SQL;
CREATE TABLE work.newobs as 
select * 
FROM newYTD_&FileDate as a
where IDnum not in (select IDnumber from old_&FileDate where year=a.year);
quit;

Re: Removing observations during create table step

MillerEL — Thu, 19 Jul 2018 18:04:19 GMT

This approach merges the files. I want to get rid of records in the new file that exist in the old file (I'm trying to remove records that I have already processed).

Re: Removing observations during create table step

MillerEL — Thu, 19 Jul 2018 20:34:30 GMT

Can't get this to work. Other thoughts?