Help with lookups - Proc Sql or Hash?

[DangIT]

Hi,

I have the following tables:

UserTxnTbl

UserID FileDate ProdGroup 

1 01Jan2015 A

1 01Feb2015 A

1 01Mar2015 A

UserCohortTbl - a user can exist in multiple Cohorts dependent on FileDate

UserID FileDate Cohort 

1 01Jan2015 01Feb2014

1 01Mar2015  01Jun2014

I am trying to create a dataset that adds Cohort to UserTxnTbl where it pulls the Cohort value that is Less Than the FileDate in the UserTxnTbl

Want

UserID FileDate ProdGroup Cohort

1 01Jan2015 A 01Feb2014

1 01Feb2015 A 01Feb2014

1 01Mar2015 A 01Jun2014

Thank you for your help!

[LinusH]

The logic is not clear. Both dates in Cohort are earlier than all file dates in Txn.
Describe the business requirement and a more thorough example for a better understanding.

[ballardw]

Also, are the dates SAS date valued numerics displayed with a Date9. format or character values?

[Ksharp]

Yes. It is not clear . what if the tables like the following, what you gonna do ?

UserTxnTbl

UserID FileDate ProdGroup 

1 01Jan2015 A

1 01Feb2015 A

1 01Mar2015 A

UserCohortTbl - a user can exist in multiple Cohorts dependent on FileDate

UserID FileDate Cohort 

1 01Jan2015 01Feb2014

1 01Feb2014 01Mar2015

1 01Mar2015  01Jun2014

[DangIT]

Sorry let me start this again:

UserTxnTbl: A table that captures all transactions of a product group at the beginning of the month for all users starting 01Jan2015 and going forward. If there is no transactions the FileDate will not exist for that month.

UserID FileDate ProdGroup 

1 01Jan2015 A

1 01Feb2015 A

1 01Mar2015 A

UserCohortTbl - A table that has analyzed the history of users and classified them into a Cohort at a point in time. From 01Feb2014 to 01Feb2015 User 1 belonged to Cohort 01Feb2014. In March 2015 they may have changed some sort of behaviour that redefined their cohort going forward.

UserID Cohort 

1 01Feb2014 

1 01Mar2015

Ultimately I am looking to add Cohort to the UserTxnTbl to identify which Cohort the user belonged to at the FileDate period.

Want

UserID FileDate ProdGroup Cohort

1 01Jan2015 A 01Feb2014

1 01Feb2015 A 01Feb2014

1 01Mar2015 A 01Mar2015

Apologies again for the vaugeness in the original post. After typing this out again, i'm thinking Proc Format might work??

Thanks again for your help

[Ksharp]

OK. Looks more clear now. Assuming I understand what do you mean.

data UserTxnTbl;
input UserID FileDate : date9. ProdGroup $;
format FileDate  date9.;
cards; 
1 01Jan2015 A
1 01Feb2015 A
1 01Mar2015 A
2 01Jan2015 A
2 01Feb2015 A
2 01Mar2015 A
run;
 
data UserCohortTbl ;
input UserID Cohort : date9.;
format Cohort date9.;
cards; 
1 01Feb2014 
1 01Mar2015
2 01Feb2014 
2 01Mar2015
;
run;
data want;
 set UserCohortTbl(rename=(Cohort=FileDate) in=ina) UserTxnTbl(in=inb);
 by UserID FileDate;
 retain Cohort;
 if first.UserID then call missing(Cohort);
 if ina then Cohort=FileDate;
 if inb;
 format Cohort date9.;
run;