topic Re: Output both members of group if one or other meets criteria in SAS Enterprise Guide

Output both members of group if one or other meets criteria

Sean_OConnor — Thu, 18 Oct 2018 11:08:39 GMT

Folks,

I have a dataset of ids and numeric values. I would like to output two datasets. The first is any id's where either id number has a value greater than zero.

The second is a dataset where either id is only 0.

See below for example;

data have;
   input id $ value ;
   datalines;
1 0
2 1
2 0
3 0
3 10
4 11
4 15
5 0
5 0
;run;

data want_1;
   input id $ value ;
   datalines;
2 1
2 0
3 0
3 10
4 11
4 15
;run;

data want_2;
   input id $ value ;
   datalines;
1 0
5 0
5 0
;run;

Re: Output both members of group if one or other meets criteria

PeterClemmensen — Thu, 18 Oct 2018 11:47:47 GMT

proc sql;
   create table want_1 as
   select * from have 
   group by id
   having sum(value)>0;
quit;

proc sql;
   create table want_2 as
   select * from have 
   group by id
   having sum(value)=0;
quit;

Re: Output both members of group if one or other meets criteria

Kurt_Bremser — Thu, 18 Oct 2018 11:55:04 GMT

Create a lookup table, and merge that back:

data have;
   input id $ value ;
   datalines;
1 0
2 1
2 0
3 0
3 10
4 11
4 15
5 0
5 0
;
run;

data lookup (keep=id);
set have;
by id;
retain flag;
if first.id then flag = 0;
if value then flag = 1;
if last.id and flag then output;
run;

data want1 want2;
merge
  have (in=a)
  lookup (in=b)
;
by id;
if b
then output want1;
else output want2;
run;

Re: Output both members of group if one or other meets criteria

Sean_OConnor — Thu, 18 Oct 2018 13:15:44 GMT

I'm just wondering how would I amend the having part of this sql query if I wanted to output if either value had a string called 'YES'

proc sql;
   create table previous_LPT_return as
   select * from Stage_2_LPT 
   group by id_document
   having value='YES';
quit;

The above example I've posted seems to only omit all the yes strings

Re: Output both members of group if one or other meets criteria

Kurt_Bremser — Thu, 18 Oct 2018 13:26:21 GMT

proc sql;
create table previous_LPT_return as
select *
from Stage_2_LPT
where id_document in (
  select distinct id_document
  from Stage_2_LPT
  where value = 'YES'
);
quit;

or you change one line in my previous code:

if value = 'YES' then flag = 1;