topic Re: Help with lookups - Proc Sql or Hash? in SAS Programming

Help with lookups - Proc Sql or Hash?

DangIT — Wed, 02 Mar 2016 20:01:39 GMT

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!

Re: Help with lookups - Proc Sql or Hash?

LinusH — Wed, 02 Mar 2016 21:37:08 GMT

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.

Re: Help with lookups - Proc Sql or Hash?

ballardw — Wed, 02 Mar 2016 21:41:33 GMT

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

Re: Help with lookups - Proc Sql or Hash?

Ksharp — Thu, 03 Mar 2016 01:24:00 GMT

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

Re: Help with lookups - Proc Sql or Hash?

DangIT — Thu, 03 Mar 2016 14:00:43 GMT

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

Re: Help with lookups - Proc Sql or Hash?

Ksharp — Fri, 04 Mar 2016 01:24:55 GMT

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;