topic Re: Identifying new observations month over month in ODS and Base Reporting

Identifying new observations month over month

SemiColon — Thu, 26 Feb 2015 15:34:22 GMT

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!

Re: Identifying new observations month over month

Reeza — Thu, 26 Feb 2015 16:55:49 GMT

Sample data/output?

Re: Identifying new observations month over month

noetsi — Thu, 26 Feb 2015 17:17:22 GMT

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;

Re: Identifying new observations month over month

SemiColon — Thu, 26 Feb 2015 20:49:10 GMT

THANK YOU!!

Re: Identifying new observations month over month

MikeZdeb — Wed, 28 Oct 2015 15:33:11 GMT

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.