BookmarkSubscribeRSS Feed
DangIT
Fluorite | Level 6

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!

 

5 REPLIES 5
LinusH
Tourmaline | Level 20
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
ballardw
Super User

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

Ksharp
Super User

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
Fluorite | Level 6

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
Super User

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 833 views
  • 0 likes
  • 4 in conversation