turn on suggestions

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

Showing results for

Find a Community

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

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-30-2017 12:56 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jl1005

03-30-2017 12:58 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to collinelliot

03-30-2017 01:13 PM

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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jl1005

03-30-2017 01:15 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to collinelliot

03-30-2017 01:19 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to collinelliot

03-30-2017 01:20 PM

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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jl1005

03-30-2017 01:25 PM

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