DATA Step, Macro, Functions and more

Help with lookups - Proc Sql or Hash?

Reply
Frequent Contributor
Posts: 81

Help with lookups - Proc Sql or Hash?

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!

 

Super User
Posts: 5,441

Re: Help with lookups - Proc Sql or Hash?

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.
Data never sleeps
Super User
Posts: 11,343

Re: Help with lookups - Proc Sql or Hash?

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

Super User
Posts: 10,046

Re: Help with lookups - Proc Sql or Hash?

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

Frequent Contributor
Posts: 81

Re: Help with lookups - Proc Sql or Hash?

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

Super User
Posts: 10,046

Re: Help with lookups - Proc Sql or Hash?

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;
Ask a Question
Discussion stats
  • 5 replies
  • 247 views
  • 0 likes
  • 4 in conversation