topic drop in a merge statement in SAS Programming

drop in a merge statement

GN0001 — Fri, 20 Aug 2021 21:31:58 GMT

Combining Data-Sets

data related1;

input Cust1-Cust22 ID;

cards;

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 5

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 6

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 7

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 8

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 9

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 10

run;

data related2;

input ID Cust1-Cust28;

cards;

1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

5 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

5 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

run;
data notrelatedmatch;
merge related1st(in=in1) related2st(in=in2);
length find $15;
by id;
if in1 and not in2 then find='not_matched1';
else if not in1 and in2 then find='not_matched2';

Hello team,

How to exclude all variables that begin with “Cu” from notrelatedmatch?

Regards,

blueblue

Re: where clause in a merge statement

PaigeMiller — Wed, 11 Aug 2021 18:53:23 GMT

How to exclude all variables that begin with “Cu” from notrelatedmatch?

On the one hand, you ask about removing variables, but your title talks about WHERE clause which removes observations. So which is it? Do you want to remove variables or observations?

Re: where clause in a merge statement

ballardw — Wed, 11 Aug 2021 19:17:23 GMT

Drop cu: ;

If you don't want them why bother keeping them through the merge??? Use data set options to Keep=ID as that is the only other variable you show.

Re: where clause in a merge statement

GN0001 — Wed, 11 Aug 2021 20:48:03 GMT

Hello Diamond,

I am talking about variables.

I used drop statement:

data notrelatedmatch(drop=cust:);

I got what I wanted. I wonder if we can use a list variable in where clause with not.

Please advise me.

Regards,

blueblue

Re: where clause in a merge statement

PaigeMiller — Wed, 11 Aug 2021 21:17:11 GMT

Yes, you can use a list of variables next to DROP=

Please go back to your ORIGINAL post in this thread and modify the title to indicate you want to remove variables in a MERGE, so that the title reflects the actual subject of the post.

Re: where clause in a merge statement

ballardw — Wed, 11 Aug 2021 21:28:09 GMT

@GN0001 wrote:

Hello Diamond,

I am talking about variables.

I used drop statement:
data notrelatedmatch(drop=cust:);
I got what I wanted. I wonder if we can use a list variable in where clause with not.

Please advise me.

Regards,

blueblue

You will have to show the exact where clause you intend to use. Hit or miss whether you guess right. Most value comparisons likely don' t us a list the way you expect, especially if want to compare multiple variables and multiple values.

Re: where clause in a merge statement

PaigeMiller — Fri, 20 Aug 2021 10:50:29 GMT

@GN0001

Please help everyone by editing the title of the ORIGINAL post to indicate that the question is not about WHERE clauses but it is about DROP= lists.

Re: where clause in a merge statement

GN0001 — Fri, 20 Aug 2021 15:36:23 GMT

Hello,
I did it in the original post.
Regards,
blueblue

Re: where clause in a merge statement

PaigeMiller — Fri, 20 Aug 2021 16:13:19 GMT

@GN0001 wrote:
Hello,
I did it in the original post.
Regards,
blueblue

I don't think you did, it still says "WHERE clause ..."

Re: where clause in a merge statement

GN0001 — Fri, 20 Aug 2021 21:22:23 GMT

I did it on the original post. I have been very busy at work and I couldn't
make it to all of them. I think when we do on the original post, the other
titles should be updated as well.

Thanks for all,
blueblue