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;
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;
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;
returned only one blank observation...
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;
This worked great! Thank you!
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;
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;
Can't get this to work. Other thoughts?
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 whereIDnumber<>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.
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;
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 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.