Help using Base SAS procedures

Remove records based on today's date

Reply
Regular Contributor
Posts: 186

Remove records based on today's date

Hi,

Lets say I have the following two table :

Table : mylib.myresult

Name
Bob
Alex
John
Nicolas

Table : mylib.myhistory

NameDate_Pres
Bob15FEB2012
Alex10JAN2013
John12DEC2012
Nicolas11APR2012

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.

Regular Contributor
Posts: 186

Re: Remove records based on today's date

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.

Super Contributor
Posts: 334

Re: Remove records based on today's date

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

Regular Contributor
Posts: 186

Re: Remove records based on today's date

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.

Super Contributor
Posts: 1,636

Re: Remove records based on today's date

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;

Regular Contributor
Posts: 186

Re: Remove records based on today's date

Thank you both for your help!

This is exactly what I wanted.

Regular Contributor
Posts: 186

Re: Remove records based on today's date

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

Regular Contributor
Posts: 186

Re: Remove records based on today's date

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

Super Contributor
Posts: 334

Re: Remove records based on today's date

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

Ask a Question
Discussion stats
  • 8 replies
  • 224 views
  • 3 likes
  • 3 in conversation