topic Re: Remove records based on today's date in SAS Procedures

Remove records based on today's date

nicnad — Thu, 28 Feb 2013 19:51:54 GMT

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.

Re: Remove records based on today's date

nicnad — Thu, 28 Feb 2013 20:00:18 GMT

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.

Re: Remove records based on today's date

esjackso — Thu, 28 Feb 2013 20:34:11 GMT

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!

Re: Remove records based on today's date

nicnad — Thu, 28 Feb 2013 20:43:29 GMT

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.

Re: Remove records based on today's date

Linlin — Thu, 28 Feb 2013 21:06:38 GMT

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;

Re: Remove records based on today's date

nicnad — Thu, 28 Feb 2013 21:35:08 GMT

Thank you both for your help!

This is exactly what I wanted.

Re: Remove records based on today's date

nicnad — Thu, 28 Feb 2013 22:23:16 GMT

What is the proper way to create a table that would contains the records that were excluded in myfinal table?

Re: Remove records based on today's date

nicnad — Thu, 28 Feb 2013 22:27:46 GMT

Also the left join still pulls the records where the check is < 90... is using inner join instead the proper way to do this?

Re: Remove records based on today's date

esjackso — Fri, 01 Mar 2013 02:22:52 GMT

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!