topic Re: merge two datasets based on name and date in SAS Programming

merge two datasets based on name and date

France — Thu, 27 Dec 2018 15:36:16 GMT

Dear all,

I'd like to merge two datasets (especially two variables) based on name(psn_name_cited) and date(earliest_filling_month)

dataset1

psn_name_cited, citation, earliest_filling_month

Apple,1,SEP2001

Facebook,2,OCT2002

dataset2

psn_name_cited,application,earliest_filling_month

Apple,5,SEP2001

Apple,2,OCT2001

Facebook,7,OCT2001

Uber,10,DEC2001

I'd like to have the following data (Want) like

psn_name_cited, earliest_filling_month,application, citation

Apple,SEP2001,5,1

Apple,OCT2001,2,0

Facebook,OCT2001,7,2

Uber,DEC2001,10,0

could you please give me some suggestion about this?

thanks in advance

Re: merge two datasets based on name and date

Kurt_Bremser — Thu, 27 Dec 2018 15:45:21 GMT

proc sort data=ds1;
by psn_name_cited earliest_filing_month;
run;

proc sort data=ds2;
by psn_name_cited earliest_filing_month;
run;

data want;
merge
  ds2 (in=ds2)
  ds1 (in=ds1)
;
by psn_name_cited earliest_filing_month;
if ds2;
if not ds1 then citation = 0;
run;

Re: merge two datasets based on name and date

France — Fri, 28 Dec 2018 00:37:28 GMT

Dear KurtBremser,

Thank you for your advice. it is totally correct.

However, could you please answer me one more question?

How can I keep the 'application' columns for a company name (i.e., psn_name_cited) if the sum number of 'citation' columns for this name is larger than zero?

for example.for the dataset have

psn_name_cited, earliest_filing_month, application, citation

apple, SEP2000,2,1

apple, SEP2001,2,0

I'd like to have the dataset want

psn_name_cited, earliest_filing_month, application, citation

apple, SEP2000,2,1

apple, SEP2001,2,0

rather than

psn_name_cited, earliest_filing_month, application, citation

apple, SEP2000,2,1

Re: merge two datasets based on name and date

Kurt_Bremser — Fri, 28 Dec 2018 07:23:00 GMT

Help us to help you.

Post example data in a readily usable form (data steps with datalines) and your code that does not give you the expected result.