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.
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;
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;
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
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.