BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
France
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
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;

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User
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;
France
Quartz | Level 8

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

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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