Merging with Many to Few

[jl1005]

Hi everyone,

I'm trying to merge two data sets together to use in creating a calculated field. Here's the code for the sorting and merging:

proc sort data=all_batting out=sorted_all;
	by descending year_id;
	run;
proc sort data=bat_weights out=sorted_bat_weights;
	where season between 1981 and 2015;
	by descending season;
	run;
data all_with_weights;
	merge sorted_all sorted_bat_weights (rename=(season=year_id));
	by year_id;
	run;

Here's the error: ERROR: BY variables are not properly sorted on data set WORK.SORTED_BAT_WEIGHTS.

Here are pictures of what each sorted data set looks like:

The sorted_all table contains multiple records for each year, where as the bat_weights only has one row per year. I'm not too sure what's going wrong.

Thank you,

Justin

[collinelliot]

You are not sorting by the same desired by variable for both sets in your merge.

[jl1005]

Isn't that what the rename is doing in the data step? It's renaming season to year_id so that they are the same variable names.

[collinelliot]

Oh. I did not look closely enough. What does your log say? Is there an error or are you just not seeing what you expect.

[collinelliot]

Other than the "by variables" error, that is. I'm wondering if something else is happening upstream.

[jl1005]

Here's the log:

1 The SAS System 22:47 Wednesday, March 29, 2017

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program (2)';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow (3)';
5 %LET _CLIENTPROJECTPATH='C:\Users\jblon\Documents\My SAS Files\baseball_2.egp';
6 %LET _CLIENTPROJECTNAME='baseball_2.egp';
7 %LET _SASPROGRAMFILE=;
8
9 ODS _ALL_ CLOSE;
10 OPTIONS DEV=ACTIVEX;
11 GOPTIONS XPIXELS=0 YPIXELS=0;
12 FILENAME EGSR TEMP;
13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14 STYLE=HtmlBlue
15 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16 NOGTITLE
17 NOGFOOTNOTE
18 GPATH=&sasworklocation
19 ENCODING=UTF8
20 options(rolap="on")
21 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22
23 GOPTIONS ACCESSIBLE;
24 data all_with_weights;
25 merge sorted_all sorted_bat_weights (rename=(season=year_id));
26 by year_id;
27 run;

ERROR: BY variables are not properly sorted on data set WORK.SORTED_BAT_WEIGHTS.
year_id=2015 bat_id=aardd001 G=53 AB=0 R=0 H=0 2B=0 3B=0 HR=. RBI=0 SB=0 CS=0 BB=0 SO=0 IBB=0 HBP=0 SH=0 SF=0 _NAME_=count
generic_out=. strikeout=1 stolen_base=. pickoff=. wild_pitch=. walk=. intentional_walk=. interference=. error=. fielders_choice=.
single=. double=. caught_stealing=. balk=. hit_by_pitch=. passed_ball=. foul_error=. triple=. other_advance=. defensive_indiff=.
wOBA=0.313 wOBAScale=1.251 wBB=0.687 wHBP=0.718 w1B=0.881 w2B=1.256 w3B=1.594 wHR=2.065 runSB=0.2 runCS=-0.392 R/PA=0.113 R/W=9.421
cFIP=3.134 FIRST.year_id=1 LAST.year_id=1 _ERROR_=1 _N_=1
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 2 observations read from the data set WORK.SORTED_ALL.
NOTE: There were 2 observations read from the data set WORK.SORTED_BAT_WEIGHTS.
WARNING: The data set WORK.ALL_WITH_WEIGHTS may be incomplete. When this step was stopped there were 0 observations and 52
variables.
WARNING: Data set WORK.ALL_WITH_WEIGHTS was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.10 seconds
cpu time 0.03 seconds

28
29 GOPTIONS NOACCESSIBLE;
30 %LET _CLIENTTASKLABEL=;
31 %LET _CLIENTPROCESSFLOWNAME=;
32 %LET _CLIENTPROJECTPATH=;
33 %LET _CLIENTPROJECTNAME=;
34 %LET _SASPROGRAMFILE=;
35
36 ;*';*";*/;quit;run;
37 ODS _ALL_ CLOSE;
38
39
2 The SAS System 22:47 Wednesday, March 29, 2017

40 QUIT; RUN;
41

[collinelliot]

You sort descending, but then  merge by year_id NOT descending.