Hi,
Lets say I have the following two table :
Table : mylib.myresult
Name |
---|
Bob |
Alex |
John |
Nicolas |
Table : mylib.myhistory
Name | Date_Pres |
---|---|
Bob | 15FEB2012 |
Alex | 10JAN2013 |
John | 12DEC2012 |
Nicolas | 11APR2012 |
I want to create a table named 'myfinal' that only shows the records from table myresult where TODAY() - DATE_PRES > 90 days.
So the table 'myfinal' would display only Alex and John record.
Those are two example tables, I really need to have a link between table myresult and table myhistory because my real history table contains a lot of names that need to be matched.
Hope you understand my problem.
Thank you for your help and time with this.
I would also like to add :
If the same name has two or more entries in the myhistory table, take only the latest one.
To explain my whole problem more clearly, here are the steps I want the procedure to make :
#1 Does the name in 'myresult' table exist in 'myhistory' table, if it does go to step #2, if not include it in 'myfinal' table.
#2 Does the latest date_pres entry for that name is more than 3 months old, if it does include it in 'myfinal' table if not do nothing.
#3 Loop each name from 'myresult' table.
Hope this is clearer.
thank you.
Myabe I am missing something because it really sounds like you want just a left join between the two table:
proc sql;
create table myfinal as
select a.*, b.date_pres
from myresult as a left join ( select name, max(date_pres) as date_pres, today() - date_pres as check
from myhistory where calculated check > 90 ) as b
on a.name = b.name
;
quit;
The real problem my be if the names are from two different sources where there could be typos / different spellings so matches will not be found.
Hope this helps!
EJ
This seems to be just what I wanted!
The only thing is that I get the following error :
ERROR: Expression using subtraction - requires numeric types.
date_pres value was entered like this :
date_pres = put(
'31DEC2012'd,date9.
);
Thank you for your help and time.
Hi,
try the modified EJ's code below:
proc sql;
create table myfinal as
select a.*, b.date_pres
from myresult as a left join ( select name, max(date_pres) as date_pres, today() - input(date_pres,date9.) as check
from myhistory where calculated check > 90 ) as b
on a.name = b.name
;
quit;
Thank you both for your help!
This is exactly what I wanted.
What is the proper way to create a table that would contains the records that were excluded in myfinal table?
Also the left join still pulls the records where the check is < 90... is using inner join instead the proper way to do this?
Given the fact that you are having issues with the check and want to output the data to multiple places you might want to go to data step merge.
But I think to fix the subquery in the sql step, since the date is a character value, you might have to input the date for the max and then use that to formulate the check :
proc sql;
create table myfinal as
select a.*, b.date_pres
from myresult as a left join ( select name, max(input(date_pres,date9.)) as date_pres, today() - calculated date_pres as check
from myhistory where calculated check > 90 ) as b
on a.name = b.name
;
quit;
If you really want to output to two datasets just do a regular left join just from myhistory (if you still want the last instance of a name then leave the max of the date code in the query) then create the check in a data step where you could out put to two datasets based on the value of the check.
Hope this helps!
EJ
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.