Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Merging with Many to Few

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-30-2017 12:56 PM
(952 views)

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

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

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.