Help using Base SAS procedures

Removing observations during create table step

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Removing observations during create table step

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;

Accepted Solutions
Solution
‎06-12-2018 04:52 PM
Esteemed Advisor
Posts: 5,529

Re: Removing observations during create table step

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


All Replies
Super User
Posts: 13,542

Re: Removing observations during create table step

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;
Occasional Contributor
Posts: 19

Re: Removing observations during create table step

returned only one blank observation...

Solution
‎06-12-2018 04:52 PM
Esteemed Advisor
Posts: 5,529

Re: Removing observations during create table step

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
Occasional Contributor
Posts: 19

Re: Removing observations during create table step

This worked great! Thank you!

Valued Guide
Posts: 591

Re: Removing observations during create table step

[ Edited ]
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
Super User
Super User
Posts: 8,106

Re: Removing observations during create table step

[ Edited ]

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 topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 160 views
  • 0 likes
  • 5 in conversation