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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.