topic Re: Need codes for each primary key id in SAS Programming

Need codes for each primary key id

anonymous_user — Sat, 08 Aug 2020 15:27:08 GMT

I'm trying to only pick those primary keys with a code equal to A2391 AND A2392 ONLY - for each primarykey.

PrimaryKey Proc Code

a156273. A2391

a156273. A2392

c456373. A2391

c456373. A2392

c456373. A2393

b784734. A2391

b784734. A2392

Should look like this

a156273. A2391

a156273. A2392

b784734. A2391

b784734. A2392

c456373. A2391

c456373. A2392

So for each primary key, I want to bring in only those who have those codes.

Re: Need codes for each primary key id

Reeza — Sat, 08 Aug 2020 16:13:21 GMT

This is one of those cases where it's easier if you reverse your logic

1. Find IDs that have something other than those two values.

2. Exclude those IDs

proc sql;
create table want as
select * 
from have 
where primaryKey not in 
         ( select distinct PrimaryKey 
            from have 
            where proc_code not in ('A2391', 'A2392')
          );
quit;

@anonymous_user wrote:

I'm trying to only pick those primary keys with a code equal to A2391 AND A2392 ONLY - for each primarykey.

PrimaryKey Proc Code

a156273. A2391

a156273. A2392

c456373. A2391

c456373. A2392

c456373. A2393

b784734. A2391

b784734. A2392

Should look like this

a156273. A2391

a156273. A2392

b784734. A2391

b784734. A2392

c456373. A2391

c456373. A2392

So for each primary key, I want to bring in only those who have those codes.

Re: Need codes for each primary key id

anonymous_user — Sat, 08 Aug 2020 22:07:22 GMT

Thank you for your response.

I'm getting a syntax error - ERROR: 22-322: after where it says:

where PrimaryKey not in

Re: Need codes for each primary key id

Reeza — Sat, 08 Aug 2020 23:53:31 GMT

Post your exact code and log. I tested your code against the data provided and it works fine with no errors so there's either a typo or your data doesn't match what's shown. I'm assuming you changed the variable and table names to match your data.

Re: Need codes for each primary key id

anonymous_user — Mon, 10 Aug 2020 12:47:17 GMT

log:

Proc Sql;

create table s.mergefinal as

select *

from s.merge

where PrimaryKey not in

ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING, ORDER,

ERROR 76-322: Syntax error

(select distinct PrimaryKey from merge

where proc_code not in ('2391','2392')

);

quit;

NOTE: The SAS System stopped processing this step because of errors.

Re: Need codes for each primary key id

Reeza — Mon, 10 Aug 2020 15:36:26 GMT

That isn't your full code and log.