topic Proc SQL Create table where none of a given primary key match a condition in SAS Programming

Proc SQL Create table where none of a given primary key match a condition

aazzarello — Wed, 25 Jan 2023 14:41:33 GMT

I'm looking to create a table through proc sql that takes a larger list with multiple entries for a primary key, and want to return rows with that have a primary key where none of a given criteria occur.

Imagine this list below as my original list called work.names . If a name has a non-null login_id value for any of its rows, I don't want any row with that name returned at all. I only want names where all rows have a NULL login_id, ideally with only one entry for each name/primary key.

Name	Login_ID
Alejandra Bravo	1394
Alejandra Bravo	NULL
Vincent Crisanti	6687
Vincent Crisanti	NULL
Stephen Holyday	NULL
Stephen Holyday	5548
Stephen Holyday	NULL
Lily Cheng	3392
Amber Morley	NULL
Amber Morley	NULL
Amber Morley	NULL
Frances Nunziata	NULL
Frances Nunziata	NULL

And from this list should return something like:

Name	Login_ID_3_1
Amber Morley	NULL
Frances Nunziata	NULL

My initial idea was to use this code:

select distinct *,
rank () over(partition by name order by LOGIN_ID) as RANK

from work.names

where RANK = 1 and missing(LOGIN_ID)

However, this does not work because rank over partition is not supported in proq sql. So perhaps there's another route to getting the data i need? It doesn't need to be a way to rank data, I just need to get rows with names that have no non-null values. Any ideas?

Re: Proc SQL Create table where none of a given primary key match a condition

PaigeMiller — Wed, 25 Jan 2023 14:53:01 GMT

This should work:

proc sql;
    create table want as select distinct *
    from have 
    group by name
    having sum(login_id^='NULL')=0;
quit;

From now on, we cannot work with data in screen captures or file attachments. We need data presented as WORKING data step code which you can type in yourself, or follow these instructions.

Re: Proc SQL Create table where none of a given primary key match a condition

aazzarello — Wed, 25 Jan 2023 15:03:28 GMT

My mistake. I did not include that the login column is a varchar column, not a numeric one. It contained numeric values in the sample I gave by coincidence, as there are some values that have other characters, so I don't believe a grouped sum would work here, would it?

Re: Proc SQL Create table where none of a given primary key match a condition

ballardw — Wed, 25 Jan 2023 15:34:10 GMT

@aazzarello wrote:

My mistake. I did not include that the login column is a varchar column, not a numeric one. It contained numeric values in the sample I gave by coincidence, as there are some values that have other characters, so I don't believe a grouped sum would work here, would it?

The sum in the suggested solution is totaling the results of the comparison: (login_id ^='NULL'). SAS returns a 1 for true and 0 for false.

So the value of the variable isn't summed, the value of the comparison result is summed and does not matter as to the variable type as long as the comparison is valid for the variable.

Re: Proc SQL Create table where none of a given primary key match a condition

PaigeMiller — Wed, 25 Jan 2023 17:55:03 GMT

Did you even try the code? It recognizes that LOGIN_ID is character. I would explain, except @ballardw already has explained.

Re: Proc SQL Create table where none of a given primary key match a condition

s_lassen — Wed, 25 Jan 2023 16:52:23 GMT

I would try to do it something like this:

Proc SQL;
  select distinct name,login_id as login_id_3_1
  from work.names base
  where not exists(select * from work.names test 
                    where name=base.name and login_id is not null);

Just one question: what do you need the LOGIN_ID for, if you already know that is always NULL?