topic Re: How to merge data sets based on a if statement? in New SAS User

How to merge data sets based on a if statement?

codyV — Fri, 23 Aug 2019 12:45:04 GMT

Hi,

I would want to merge 2 data sets, where in the tables look like this:

table1:

SID CID Flag country

1 2 name US

2 4 name US

5 non-name NZ

7 non-name UK

table 2:

SID CID FLAG Country status

1 2 name US active

2 4 name US active

5 non-name NZ active

7 non-name UK active

10 6 name UK active

so i want to merge the above tables

if SID is not equal to blank or flag is name then merge the table by SID and country

else if SID is equal to blank or flag is non-name then merge the above table by CID and country.

Note:- if there is an SID it will have the flag as 'name'

The data step i am using is very basic and it doesnt work.

data want;

set have1 (keep= SID CID FLAG COUNTRY)

have2 (keep=SID CID FLAG COUNTRY STATUS);

by SID CID COUNTRY

IF FLAG = NAMED output SID;

else output CID;

run;

i wonder if there is a better way to merge with this condition. Appreciate your time and help!

Re: How to merge data sets based on a if statement?

novinosrin — Fri, 23 Aug 2019 12:47:16 GMT

Hi @codyV Can you please post the expected output for the input samples?

Re: How to merge data sets based on a if statement?

codyV — Fri, 23 Aug 2019 12:53:32 GMT

Accepted Output:
SID CID Flag country status
1 2 name US active
2 4 name US active
5 non-name NZ active
7 non-name UK active

I need to merge the data by SID for the flag- name which is the first priority!
Appreciate your help!

Re: How to merge data sets based on a if statement?

PeterClemmensen — Fri, 23 Aug 2019 12:54:28 GMT

Hi and welcome to the SAS Communities 🙂

What does your desired result look like? Makes it easier to provide a usable code answer.

Also, if your data is representable, your request is equivalent to merging on CID and Country since they are both present and non missing in both data sets?

Re: How to merge data sets based on a if statement?

novinosrin — Fri, 23 Aug 2019 13:00:07 GMT

Hope this helps

data table1;
input SID       CID      Flag   $           country $;
cards;
1            2         name              US
2            4         name              US
.              5         non-name       NZ
.              7         non-name       UK
;

data table2;
input SID       CID     (FLAG             Country          status) ($);
cards;
1            2         name              US                active
2            4         name              US                active
.              5         non-name       NZ                active
 .             7         non-name       UK                active
10          6         name               UK               active
;

proc sql;
create table want as
select a.*,status
from table1 a, table2 b
where (a.SID>. or a.flag='name') and (a.sid=b.sid and a.country=b.country) or
(a.SID=. or a.flag='non-name') and (a.cid=b.cid and a.country=b.country);
quit;

Re: How to merge data sets based on a if statement?

codyV — Fri, 23 Aug 2019 13:09:56 GMT

Apologies for the mere example given earlier!

This is how the tables look and the expected result in precise:

Table 1:-

SID	CID	FLAG	COUNTRY
1	3	name	US
2		name	US
4	4	name	UK
	6	non-name	AUS
	8	non-name	NZ
	9	non-name	UK
9	7	name	US
	10	non-name	US
Table 2:-
SID	CID	Flag	COUNTRY	STATUS
1	3	name	US	active
2		name	US	active
5		name		active
	6	non-name	AUS	active
7		name		active
8		name		active
	8	non-name	NZ	active
	9	non-name	UK	active
9	7	name	US	active


Result:-
SID	CID	Flag	COUNTRY	STATUS
1	3	name	US	active
2		name	US	active
	6	non-name	AUS	active
	8	non-name	NZ	active
	9	non-name	UK	active
9	7	name	US	active

Re: How to merge data sets based on a if statement?

novinosrin — Fri, 23 Aug 2019 13:10:49 GMT

Hello @codyV

On taking a closer look, methinks all variables in Table1 can be used as keys

data table1;
input SID       CID      Flag   $           country $;
cards;
1            2         name              US
2            4         name              US
.              5         non-name       NZ
.              7         non-name       UK
;

data table2;
input SID       CID     (FLAG             Country          status) ($);
cards;
1            2         name              US                active
2            4         name              US                active
.              5         non-name       NZ                active
 .             7         non-name       UK                active
10          6         name               UK               active
;


data want ;
if _n_=1 then do;
   dcl hash H (dataset:'table1') ;
   h.definekey  (all:'y') ;
   h.definedata (all:'y') ;
   h.definedone () ;
end;
set table2;
if h.find()=0;
run;

Re: How to merge data sets based on a if statement?

novinosrin — Fri, 23 Aug 2019 13:18:34 GMT

data table1;
infile cards expandtabs truncover;
input SID       CID      Flag   $           country $;
cards;
1	3	name	US	 
2	 .	name	US	 
4	4	name	UK	 
 .	6	non-name	AUS	 
 .	8	non-name	NZ	 
 .	9	non-name	UK	 
9	7	name	US	 
 .	10	non-name	US
;

data table2;
infile cards expandtabs truncover;
input SID       CID     (FLAG             Country          status) ($);
cards;
1	3	name	US	active
2	. 	name	US	active
5	. 	name	 .	active
. 	6	non-name	AUS	active
7	. 	name	 .	active
8	. 	name	 .	active
. 	8	non-name	NZ	active
. 	9	non-name	UK	active
9	7	name	US	active
;


data want ;
if _n_=1 then do;
   dcl hash H (dataset:'table1') ;
   h.definekey  (all:'y') ;
   h.definedata (all:'y') ;
   h.definedone () ;
end;
set table2;
if h.find()=0;
run;

RESULT:

1 3 name US active
2 . name US active
. 6 non-name AUS active
. 8 non-name NZ active
. 9 non-name UK active
9 7 name US active

Re: How to merge data sets based on a if statement?

codyV — Sun, 25 Aug 2019 20:45:57 GMT

Proc sql did work but it has been taking too much time to run the data behind....! Many Thanks & appreciate your support

Re: How to merge data sets based on a if statement?

codyV — Sun, 25 Aug 2019 20:48:02 GMT

if you help me understand the do statement bit, as to how will it help me provide the expected result

Re: How to merge data sets based on a if statement?

novinosrin — Sun, 25 Aug 2019 21:04:02 GMT

The statements between DO-END creates an instance of a look up table in memory aka Hash memory table. During execution of the datastep, values are loaded from table1 into the LOOK UP table. It's instance is name h. It is of type hash which a non-scalar value or in other words will hold instance pointing to the values from table1 that is loaded.

The find() method is a look up method to look up values in table1 from table2 using key values that match.When the match is found, the dot-notation method results in 0 implicitly for true matches and a non zero values for non-matches.

The condition if h.find()=0 requests for only true matches to be written to the output. In a nutshell, we are doing a lookup operation that's an equivalent of a join.

Hope that helps.