topic Re: How to subset dataset if value of a variable is same for an ID in SAS Programming

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

d0816 — Thu, 27 Jun 2019 14:10:06 GMT

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.

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

PeterClemmensen — Thu, 27 Jun 2019 14:21:25 GMT

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;

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

d0816 — Mon, 01 Jul 2019 12:56:19 GMT

@PeterClemmensen

Thank you. This worked perfect.

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

PeterClemmensen — Mon, 01 Jul 2019 12:57:44 GMT

@d0816 glad to help 🙂