How to subset dataset if value of a variable is same for an ID

[d0816]

Hello,

I have following dataset. (There are other variables too but this sample dataset has only two variables)

ID	variable1
1	NA
2	C
2	NC
3	NA
3	C
3	NC
4	NA
4	NA
4	NA
4	NA
5	NC
5	NC
5	NC

I would like to subset into two dataset where dataset 1 is: (This data will contains all rows for an ID if the value for variable 1 is NA.)

ID	variable1
1	NA
4	NA
4	NA
4	NA
4	NA

and Dataset 2 is:

ID	variable1
2	C
2	NC
3	NA
3	C
3	NC
5	NC
5	NC
5	NC

Would appreciate your help with this.

Thank you.

[PeterClemmensen · Accepted solution]

Here is one way

data have;
input ID variable1 $;
datalines;
1 NA
2 C
2 NC
3 NA
3 C
3 NC
4 NA
4 NA
4 NA
4 NA
5 NC
5 NC
5 NC
;

proc sql;
    create table one as
    select * from have
    group by ID
    having n(variable1)=sum(variable1='NA');
    create table two as
    select * from have
    group by ID
    having n(variable1) ne sum(variable1='NA');
quit;

[d0816]

@PeterClemmensen

Thank you. This worked perfect.

[PeterClemmensen]

@d0816 glad to help 🙂