The SAS Output Delivery System and reporting techniques

Identifying new observations month over month

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

Posted in reply to SemiColon

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;

View solution in original post


All Replies
Super User
Posts: 19,822

Re: Identifying new observations month over month

Posted in reply to SemiColon

Sample data/output?

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

Re: Identifying new observations month over month

Posted in reply to SemiColon

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

Re: Identifying new observations month over month

THANK YOU!!

Valued Guide
Posts: 765

Re: Identifying new observations month over month

[ Edited ]
Posted in reply to SemiColon

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.

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

Discussion stats
  • 4 replies
  • 547 views
  • 0 likes
  • 4 in conversation