JOIN 1 to many with dates Question

IgawaKei29 — Thu, 14 Nov 2019 14:21:33 GMT

So I have an interesting problem that I am hoping I can find an answer for.

I have a work file that I created with: (Work.Test_1)

Team Number

Unique Player ID

Date Played (this can be many dates) (DATETIME22.3 format) <-- I know I will need to select a MAX and convert to a YYYYMM format.

I have another table (Work.new_table) that I want to join to obtain Category.

The issue is that this table is sorted with a date format of 201911.

So what I am looking to do is something like below

Proc Sql;
create table work.test_2 as
select

Team_Number,
Unique_Player_ID,
Category

from work.new_table

where Team_Number in (select Team_Number from work.test_1) and 
Unique_Player_ID in (select Unique_Player_ID from work.test_1) and
Date in select (Max Date_Played)

order by 1;
quit;

Thank you

Re: JOIN 1 to many with dates Question

unison — Thu, 14 Nov 2019 20:00:00 GMT

It's quite unclear what you're wanting without a sample dataset. If my solution isn't what you're looking for, I strongly suggest posting a 'have' and 'want' data sample so that others may help answer your question -- create the 'have' as a data step as opposed to posting pictures or anything like that.

Here's an example:

1. Using SASHELP.TIMEDATA to create an analogous dataset to the one I think you have:

proc format;
	value team
	1-2 = 1
	3-5 = 2
	;
	value cat
	1-2 = "Baseball"
	3-5 = "Basketball"
	;
run;

proc sort data=sashelp.timedata(where=(volume le 5)) 
	out=raw(rename=(volume=player_id datetime=date_played) ); 
	by volume datetime;
run;

proc sort data=raw nodupkey out=players(keep=player_id);
by player_id;
run;

2. 'have' and 'player_info' should be in the form of what you're calling test_1 and new_table

data have(drop=count);
	set raw;
	count+1;
	by player_id;
	if first.player_id then count=1;
	if count le 5 then output;
run;

data player_info;
set players;
team_number=input(put(player_id,team.),8.);
category=put(player_id,cat.);
run;

3. Merge the results so that player info is joined to the set with the dates_played.

data want;
merge have(in=a) player_info(in=b);
by player_id;
if a;
run;

-unison

topic JOIN 1 to many with dates Question in SAS Programming

JOIN 1 to many with dates Question

Re: JOIN 1 to many with dates Question