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!
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;
Sample data/output?
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;
THANK YOU!!
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.