Solved
New Contributor
Posts: 2

# Identifying new observations month over month

I have a monthly data table (current_month)  that I need to compare to the previous months file (p1_Month) and identify the new observations. Once the new ones are identified I would like to concatenate (New) to the end of one of the variables.  any suggestions would be greatly appreciated!

Accepted Solutions
Solution
‎02-26-2015 12:17 PM
Occasional Contributor
Posts: 9

## Re: Identifying new observations month over month

This finds records in one table not in another. You culd use it to find which records in a new month not in the previous month.

To chose what is in t1 but not t2

PROCSQL;

CREATETABLE SASUSER.p ASSELECT T1.SSN

FROM
SASUSER.T2
AS T2

RIGHT JOIN SASUSER.T1
AS T1 ON
(T2.SSN = T1.SSN)

WHERE
T2.SSN IS
NULL ;

QUIT;

All Replies
Super User
Posts: 23,357

## Re: Identifying new observations month over month

Sample data/output?

Solution
‎02-26-2015 12:17 PM
Occasional Contributor
Posts: 9

## Re: Identifying new observations month over month

This finds records in one table not in another. You culd use it to find which records in a new month not in the previous month.

To chose what is in t1 but not t2

PROCSQL;

CREATETABLE SASUSER.p ASSELECT T1.SSN

FROM
SASUSER.T2
AS T2

RIGHT JOIN SASUSER.T1
AS T1 ON
(T2.SSN = T1.SSN)

WHERE
T2.SSN IS
NULL ;

QUIT;

New Contributor
Posts: 2

THANK YOU!!

Valued Guide
Posts: 765

## Re: Identifying new observations month over month

[ Edited ]

Hi.  If you want to CONCATENATE observations, you could  do a UNION in SQL, not a JOIN. It's easy to concatenate only the new observations since the default behavior of UNION is to de-duplicate the resulting data set.  Using example data from SQL Component Dictionary query-expression ...

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473694.htm

data old;
input flight dest :\$3. @@;
datalines;
145 ord 156 was 188 lax 193 fra 207 lon
;

data new;

input flight dest :\$3. @@;
datalines;
193 fra 207 lon 311 sja
;

proc sql;
create table newer as select * from old union select * from new;
quit;

data set OLD
Obs flight dest
1 145 ord
2 156 was
3 188 lax
4 193 fra
5 207 lon

data set NEW

Obs flight dest
1 193 fra
2 207 lon
3 311 sja

data set NEWER (only NEW observations from data set TWO appended to data set ONE)
Obs flight dest
1 145 ord
2 156 was
3 188 lax
4 193 fra
5 207 lon
6 311 sja

NOTE;  You could do the same thing with two PROCs.

proc append base=old data=new;
run;

proc sort data=old noduprecs;
by _all_;
run;

You get the same resutls as above, but they are in data set OLD not data set NEWER.  You could use ...

create table old as select * from old union select * from new;

in PROC SQL but you would get this warning ...

WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem.

🔒 This topic is solved and locked.