topic Re: Best way to proc merge? in SAS Studio

Best way to proc merge?

sastuck — Tue, 06 Mar 2018 00:54:09 GMT

My firm dataset:

row

date

ticker

cusip

annual_return

20171229

1387210

91.844825974

20171229

AABA

2134610

28.212199886

20141231

AAC

30710

117.77973147

My CEO salary dataset:

row

exec_fullname

coname

ceoann

salary

year cusip

Daniel P. Amos

AFLAC INC

CEO

1441.1

2016

105510

Victor L. Lund

TERADATA CORP

CEO

526.776

2016

88076W10

Carol A. Bartz, Ph.D.

ALTABA INC

CEO

1000

2010

2134610

Carol A. Bartz, Ph.D.

ALTABA INC

CEO

735.025

2011

2134610

Richard L. Carrin Rexach

POPULAR INC

CEO

838.26

2010

73317470

Richard L. Carrin Rexach

POPULAR INC

CEO

1316.282

2011

73317470

Richard L. Carrin Rexach

POPULAR INC

CEO

1400

2012

73317470

Richard L. Carrin Rexach

POPULAR INC

CEO

1400

2013

73317470

Richard L. Carrin Rexach

POPULAR INC

CEO

1400

2014

73317470

In the end I would like these variables in the same dataset:

Firm CEO Salary Annual Return Year

I am new to SAS and the last time I did a proc merge it was much simpler than this. What suggestions do you have for the best way to merge these datasets?

Thanks so much!

Re: Best way to proc merge?

Astounding — Tue, 06 Mar 2018 01:30:01 GMT

The first decision to make is how they match up. Is matching on CUSIP enough? Do you need to get the YEAR involved as well? There's no programming at this point, just decisions as to what constitutes a match.

Re: Best way to proc merge?

sastuck — Tue, 06 Mar 2018 01:38:51 GMT

I am looking into the effect of firm performance on CEO compensation, so I feel like the year is essential because the return in that particular year needs to be matched to that year’s CEO’s salary so that I can study the relationship between these two variables

Re: Best way to proc merge?

ChrisBrooks — Tue, 06 Mar 2018 02:05:53 GMT

Just for the record as well there is no Proc Merge - you can use a MERGE statement or an SQL JOIN. In practice the decision about which to choose often seems to boil down to personal preference more than anything else.

Re: Best way to proc merge?

Astounding — Tue, 06 Mar 2018 02:32:56 GMT

So would it be a match to compare the CUSIP and YEAR from the CEO salary data set, vs. the CUSIP and the year portion of the date from the FIRM data set?

Re: Best way to proc merge?

sastuck — Tue, 06 Mar 2018 03:11:11 GMT

I'm not sure. What do you suggest? The important thing is that I have the following in the same data set:

Firm CEO Salary Annual Return Year

Re: Best way to proc merge?

Astounding — Tue, 06 Mar 2018 08:45:01 GMT

That's really something you have to decide before programming can begin. You're the one looking at the data, and you're the one who is familiar with what the fields mean. If you don't know which observations should match and which shouldn't, there's no way that SAS can help.

Re: Best way to proc merge?

sastuck — Tue, 06 Mar 2018 15:23:59 GMT

I want to merge by year. But my CSRP dataset doesn't have this variable. How can I use the ret variable (which includes the year) to make this new "year" variable for each observation?

This is what the ret variable looks like:

20161230

Ret

3334

1387210

ALCOA CORP

331313

55718

3334

1387210

-3.0721

16347

20171229

3334

1387210

ALCOA CORP

331313

55718

3334

1387210

91.844825974

16752

20171229

7375

2134610

AABA

ALTABA INC

AABA

541512

55964

19523

7375

2134610

28.212199886

14945

20141231

6799

30710

AAC

A A C HOLDINGS

AAC

523910

55038

6799

30710

117.77973147

14945

20151231

6799

30710

AAC

A A C HOLDINGS

AAC

523910

55038

6799

30710

-38.35712285

14945

20161230

6799

30710

AAC

A A C HOLDINGS

AAC

523910

55038

6799

30710

-62.01467799

14945

20171229

6799

30710

AAC

A A C HOLDINGS

AAC

523910

55038

6799

30710

24.309233988

15580

20101231

6320

4820910

AAME

ATLANTIC AMERIC

AAME

524113

6320

4820910

58.593603499

15580

20111230

6320

4820910

AAME

ATLANTIC AMERIC

AAME

524113

6320

4820910

-2.061186337

15580

20121231

6320

4820910

AAME

ATLANTIC AMERIC

AAME

524113

6320

4820910

20121204

20121227

20121214

60.498060166

15580

20131231

6320

4820910

AAME

ATLANTIC AMERIC

AAME

524113

6320

4820910

33.157366168

15580

20141231

6320

4820910

AAME

ATLANTIC AMERIC

AAME

524113

6320

4820910

-0.41107092

15580

20151231

6320

4820910

AAME

ATLANTIC AMERIC

AAME

524113

6320

4820910

24.446561041

15580

20161230

6320

4820910

AAME

ATLANTIC AMERIC

AAME

524113

6320

4820910

-17.44246098

15580

20171229

6320

4820910

AAME

ATLANTIC AMERIC

AAME

524113

6320

4820910

-16.653317

10517

20101231

20120508

7359

253530

AAN

AARONS INC

AAN

443111

5674

7016

7359

253530

47.612254611

10517

20111230

7359

253530

AAN

AARONS INC

AAN

443111

5674

7016

7359

253530

31.114473349

10517

20121231

7359

253530

AAN

AARONS INC

AAN

443141

5674

7016

7359

253530

6.2307808656

10517

20131231

Re: Best way to proc merge?

Astounding — Tue, 06 Mar 2018 15:35:46 GMT

That one's fairly straightforward. Since your RET values are numeric:

year = int( ret/10000 );

Re: Best way to proc merge?

sastuck — Tue, 06 Mar 2018 16:01:01 GMT

I have my new variable year--thanks for the help!

now I am trying to merge by year:

* 3. Merge CEO data and firm data by year in a data step; 
DATA ceo_firm ; 
  MERGE ceos_sorted firms_sorted; 
  BY year; 
RUN;

but I am getting an error message: "variable CUSIP has been defined as both character and numeric. Isn't this odd given the fact that I was only trying to merge by year? Thanks!

Re: Best way to proc merge?

Cynthia_sas — Tue, 06 Mar 2018 16:11:39 GMT

Hi:
SAS builds the new dataset from ALL the variables in ALL the datasets being combined. So when it finds a mismatch in the type for any of the variables you will have in the new dataset, it will complain.

Do a PROC CONTENTS on both your files -- ceos_sorted and firms_sorted and double check the CUSIP variable. My guess is that SAS is correct and you have a type mismatch that you need to fix.

cynthia

Re: Best way to proc merge?

Astounding — Tue, 06 Mar 2018 16:11:51 GMT

Think of it this way. There's only one CUSIP variable being saved. So SAS needs to decide before bringing in any data whether CUSIP should be character or numeric. It's getting mixed messages when it examines the incoming data sets.

Once again, your decision comes before any programming begins. CUSIP needs to be defined consistently in the two data sets. Would you like it to be character or numeric? Either is possible (and easy) to accomplish.

Re: Best way to proc merge?

sastuck — Tue, 06 Mar 2018 16:12:55 GMT

I suppose its arbitrary really. How do I just make both numeric?

Re: Best way to proc merge?

Astounding — Tue, 06 Mar 2018 16:16:11 GMT

As @Cynthia_sas mentioned, you need to run PROC CONTENTS so you know which data set contains the character version. For illustration purposes, assume it's FIRMS_SORTED. Then you could run:

data firms_sorted_new;

set firms_sorted (rename=(cusip=temp));

cusip = input(temp, 12.);

drop temp;

run;

That assumes that CUSIP never exceeds 12 characters originally.

Re: Best way to proc merge?

sastuck — Fri, 16 Mar 2018 17:22:44 GMT

@Astounding, Good Afternoon,

I have run into some issues again in merging my datasets. One of the issues is that I want only CEOs in the merged dataset, and this community helped me find code to do that, but, even with this code, there remain other executives in my dataset. Do you know why this isn't working? Here's what my dataset and code look like:

row

exec_fullname

coname

cfoann

salary

year

date

ret annual_return

Timothy J. Romenesko

AAR CORP

468.18

2010

20101231

0.023594

47.612254611

Richard J. Poulton

AAR CORP

367.2

2010

20101231

0.099805

46.882111929

Terry D. Stinson

AAR CORP

338.13

2010

20101231

0.03667

53.066450461

Robert J. Regan

AAR CORP

367.2

2010

20101231

0.060538

350.47620897

Robert E. Switz

ADC TELECOMMUNICATIONS INC

CEO

767.831

2010

20101231

0.023277

49.879140893

Patrick D. O'Brien

ADC TELECOMMUNICATIONS INC

349.936

2010

20101231

0.056304

16.220864139

Jeffrey D. Pflaum

ADC TELECOMMUNICATIONS INC

310.378

2010

20101231

-0.005556

5.0879452755

James G. Mathews

ADC TELECOMMUNICATIONS INC

344.865

2010

20101231

0.159607

20.357696705

Kimberly Hartwell

ADC TELECOMMUNICATIONS INC

310.378

2010

20101231

0.105997

32.401213598

Gerard J. Arpey

AMERICAN AIRLINES GROUP INC

CEO

669.646

2010

20101231

0.363636

90.475977378

Daniel P. Garton

AMERICAN AIRLINES GROUP INC

530.478

2010

20101231

0.174079

60.277798513

Gary F. Kennedy, Esq.

AMERICAN AIRLINES GROUP INC

502.543

2010

20101029

0.489189

-0.421830073

Robert William Reding

AMERICAN AIRLINES GROUP INC

530.479

2010

20101231

0.191871

290.00037099

Thomas W. Horton

AMERICAN AIRLINES GROUP INC

618.135

2010

20101231

0.141989

30.597781935

Isabella D. Goren

AMERICAN AIRLINES GROUP INC

423.993

2010

20101231

-0.162892

10.080476093

libname paper "~/425/425_Final_Paper";

*import compustat dataset;
PROC IMPORT out=paper.CompuStat_Execucomp
	datafile="~/425/425_Final_Paper/CompuStat_Execucomp.csv"
	DBMS=CSV replace;
	getnames= yes;
	guessingrows=2000;
run;

*keep only CEOs;
data CEO;
	set paper.Compustat_ExecuComp;
 	if CEOANN = 'CEO' then output CEO;
run;

*import csrp dataset;
PROC IMPORT out=paper.CSRP_Monthly_Stock
	datafile="~/425/425_Final_Paper/CSRP_MonthlyStock.csv"
	DBMS=CSV replace;
	getnames= yes;
/*guessingrows=max;*/
run;

*delete rows with missing cusips;
data paper.CSRP_Monthly_Stock;
	set paper.CSRP_Monthly_Stock;
	if nmiss(cusip) > 0 then delete;
run;

*create new variable year;
data paper.CSRP_Monthly_Stock;
	set paper.CSRP_Monthly_Stock;
	year = int( date/10000 );
run;

*delete rows with missing return data;
data paper.CSRP_Monthly_Stock;
	set paper.CSRP_Monthly_Stock;
	if nmiss(ret) > 0 then delete;
run;

*sort by ticker;
proc sort 
	data=paper.CSRP_Monthly_Stock;
	by ticker date;
run;

data paper.CSRP_Monthly_Stock;
	set paper.CSRP_Monthly_Stock;
	by ticker date;
	retain annual_return 1;
	annual_return = annual_return * (1 + RET);
	month = int( mod(date,10000) / 100);
	if month = 12 or last.ticker;
	annual_return = (annual_return - 1) * 100;
	output;
	annual_return = 1;
run;

/*merging*/

/*keep only CEOs*/
data CEO;
	set paper.Compustat_ExecuComp;
 	if CEOANN = 'CEO' then output CEO;
run;

* 1. Sort CEO data by cusip and year & save sorted file as ceo_sorted ; 
PROC SORT DATA=paper.Compustat_ExecuComp OUT=ceos_sorted; 
  BY year; 
RUN; 

* 2. Sort firm data by cusip and year & save sorted file as firms_sorted ; 
PROC SORT DATA=paper.CSRP_Monthly_Stock OUT=firms_sorted; 
  BY year; 
RUN; 

libname paper "~/425/425_Final_Paper";

/*keep only CEOs*/
data CEO;
	set paper.Compustat_ExecuComp;
 	if CEOANN = 'CEO' then output CEO;
run;

* 3. Merge CEO data and firm data by year in a data step; 
DATA ceo_firm ; 
  MERGE ceos_sorted_new firms_sorted; 
  BY year; 
RUN; 

/*use proc contents to see if there is a type mismatch*/
proc contents 
 	data=paper.CSRP_Monthly_Stock;
run;

proc contents 
 	data=paper.Compustat_ExecuComp;
run;

* change cusip to numeric variable; 
data ceos_sorted_new;
set ceos_sorted (rename=(cusip=temp));
cusip = input(temp, 12.);
drop temp;
run;

Thanks for the help!

Re: Best way to proc merge?

Astounding — Sat, 17 Mar 2018 00:33:43 GMT

It's a little too much to do a detailed analysis here, but ...

Only one data set is subset to contain CEOs only: CEO

Other data sets are being merged in, but are not subset in this way. The mismatches can introduce non-CEO observations (or at least can introduce missing values).

Re: Best way to proc merge?

sastuck — Sat, 17 Mar 2018 14:23:21 GMT

Is there a simple way to make the "keep only CEOs" code overwrite the dataset I already have so that I don't have this issue? I don't need the non-CEO data, so I would like to just get rid of it.

Thanks!

Re: Best way to proc merge?

Astounding — Sat, 17 Mar 2018 15:36:40 GMT

That's not the issue here. You need to learn about this program:

data combined;

merge ceo (in=a) other_data (in=b);

by id;

***** something here;

run;

You need to learn how to use the variables A and B to select any of these combinations:

matches only
mismatches where the ID appears in CEO but not in OTHER_DATA
mismatches where the ID appears in OTHER_DATA but not in CEO
IDs that appear in CEO, whether or not they also appear in OTHER_DATA
IDs that appear in OTHER_DATA, whether or not they also appear in CEO

Re: Best way to proc merge?

sastuck — Sat, 17 Mar 2018 16:36:02 GMT

Thanks.

in the code, are A and B symbolizing the different datasets?

Re: Best way to proc merge?

Cynthia_sas — Sat, 17 Mar 2018 18:01:07 GMT

Hi:

Here's a concrete example to illustrate the use of the IN= option on the MERGE statement. The IN= option creates a temporary variable that holds either a 1 or a 0 value depending on whether the dataset contributes any information to the current Program Data Vector (PDV) being populated by the MERGE (the output data comes from the PDV). Your DATA step will create a PDV area for every dataset being created.

The nice thing about using IN= is that you can do multiple tests and multiple outputs based on your conditions. For example, Consider this data. WORK.ONE is the "name" file. It has COMPANY, JOB, YEAR and NAME. WORK.TWO is the "amount" file. It has COMPANY, JOB and AMOUNT:

data one;
  infile datalines;
  input company $ job $ year name $;
return;
datalines;
aa job1 2012 Alan
aa job4 2012 Alexa
bb job1 2012 Bob
cc job1 2012 Carla
dd job1 2012 Dana
dd job9 2012 Dave
ee job1 2012 Edith
;
run;

data two;
  infile datalines;
  input company $ job $ amount ;
return;
datalines;
aa job1 101
aa job2 102
aa job3 103
bb job1 201
cc job2 302
cc job3 303
dd job1 401
dd job9 409
ee job1 501
ff job1 601
;
run;

There are matches and non-matches and multiple jobs in both files. Merging on COMPANY and JOB gives me a unique combo of variables for the MERGE. However, if I do a simple MERGE, with only a BY, I will probably get undesirable results because the default is for all the matches and nonmatches to be put into the output dataset:

Given the data, I have some issues. I do have matches, but they are matches for a variety of jobs. What if I only want to get matches for the rows with JOB1? Well, that's pretty easy to do, but what about row #6 for Carla, she was in my WORK.ONE dataset, but there wasn't a match for her row in WORK.TWO - where do I want her row, in with matches or in with non-matches? Or do I need to go back and fix the data?

Let's look at the first use of a MERGE with IN=, where I save the values of the temporary variables into the output dataset, so I can examine them:

And, if I didn't want any further filter on JOB or YEAR, then this might be OK. But what if I DO want to separate the observations into one set of files where JOB=JOB1 and another set of files where JOB NE JOB1??? Then I have to modify the logic in the program and output to more datasets:


data match_both match_job1 match_not_job1
     name_only name_job1 name_not_job1
     amount_file_only amount_for_job1 amount_not_job1;
  merge one(in=in_name) two(in=in_amt);
  by company job;
  if in_name=1 and in_amt=1 then do;
     output match_both;
	 if job='job1' then output match_job1;
	 else if job ne 'job1' then output match_not_job1;
  end;
  else if in_name=1 and in_amt=0 then do;
     output name_only;
	 if job='job1' then output name_job1;
	 else if job ne 'job1' then output name_not_job1;
  end;
  if in_name=0 and in_amt=1 then do;
     output amount_file_only;
     if job = 'job1' then output amount_for_job1;
	 else if job ne 'job1' then output amount_not_job1;
  end; 
run;

proc print data=match_both;
  title '1) Match on Company and Job';
  title2 'Without filter on JOB';
run;

proc print data=name_only;
  title '2) Have name, but no amount';
  title2 'Without filter on JOB';
run;

proc print data=amount_file_only;
  title '3) Have amount, but not in name file';
  title2 'Without filter on JOB';
run;
 
proc print data=match_job1;
  title '4) Match on Company and Job';
  title2 'Only filter JOB1';
run;

proc print data=name_job1;  
  title '5) Have name, but no amount';
  title2 'Only filter JOB1';
run;

proc print data=amount_for_job1;  
  title '6) Have amount, but not in name file';
  title2 'Only filter JOB1';
run;

proc print data=match_not_job1;
  title '7) Match on Company and Job';
  title2 'NOT JOB1 Matches';
run;

proc print data=name_not_job1;
  title '8) Have name, but no amount';
  title2 'Name is NOT JOB1 have no match in amount file';
run;

proc print data=amount_not_job1;
  title '9) Have amount, but not in name file';
  title2 'Amount is NOT JOB1, have no match in name file';
run;

And the output has been filtered even further so that all the JOB1 rows -- matches and non-matches are in separate files so you can deal with them. Here's the output from all of the above PROC PRINTs, slightly rearranged:

There might be other ways you want to create the output datasets. You might want fewer datasets, you might want to treat the matches and nonmatches differently for the JOB1 rows. At this point, it is just a matter of changing the program logic.

Hope this helps,

cynthia