BookmarkSubscribeRSS Feed
nicnad
Fluorite | Level 6

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.

8 REPLIES 8
nicnad
Fluorite | Level 6

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.

esjackso
Quartz | Level 8

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

nicnad
Fluorite | Level 6

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.

Linlin
Lapis Lazuli | Level 10

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;

nicnad
Fluorite | Level 6

Thank you both for your help!

This is exactly what I wanted.

nicnad
Fluorite | Level 6

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

nicnad
Fluorite | Level 6

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

esjackso
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 700 views
  • 3 likes
  • 3 in conversation