BookmarkSubscribeRSS Feed
jl1005
Obsidian | Level 7

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:

 

 

sorted_1.JPGsorted_2.JPGThe 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

6 REPLIES 6
collinelliot
Barite | Level 11

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

jl1005
Obsidian | Level 7
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
Barite | Level 11

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
Barite | Level 11

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

jl1005
Obsidian | Level 7

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
Barite | Level 11

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

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 1001 views
  • 1 like
  • 2 in conversation