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.
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;
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 glad to help 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.