BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MillerEL
Obsidian | Level 7

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;
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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;
PG

View solution in original post

10 REPLIES 10
ballardw
Super User

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;
MillerEL
Obsidian | Level 7

returned only one blank observation...

PGStats
Opal | Level 21

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;
PG
MillerEL
Obsidian | Level 7

This worked great! Thank you!

MillerEL
Obsidian | Level 7

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;
PGStats
Opal | Level 21

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;
PG
MillerEL
Obsidian | Level 7

Can't get this to work. Other thoughts?

SuryaKiran
Meteorite | Level 14
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.

Thanks,
Suryakiran
Tom
Super User Tom
Super User

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;
MillerEL
Obsidian | Level 7

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). 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1202 views
  • 0 likes
  • 5 in conversation