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 🙂
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.