Create subset by enumeration variable

[AZIQ1]

Hi,

Data have:

str           recordid               Var1       Var2

1              7                              1              1

1              8                              0              1

2              8                              4              1

1              10                           7              8

2              10                           3              9

3              10                           2              10

I want to create a subset of data with the recordid that has all 3 observations 

data want

str           recordid               Var1       Var2

1              10                           7              8

2              10                           3              9

3              10                           2              10

[RW9]

I am not typing that text into SAS (write it as a datastep), so this is untested, but something like:

proc sql;
  create table WANT as 
  select  *
  from    HAVE
  having count(RECORDID) = 3;
quit;

[AZIQ1]

The log says:
NOTE: The query requires remerging summary statistics back with the original data

[RW9]

Yes, and?  It is a note which tells you that you are selecting values based on a statistic.  It doesn't matter too much.  If you fiddle around with it, maybe try adding distinct *.  As I said, not typing the test data in to check.  This post explains what the note refers to:

https://communities.sas.com/t5/SAS-Procedures/Proc-Sql-Remerging-summary-statstics/td-p/28608

[data_null__]

If the quality of the question does not meet your standards why don't you just ignore it.

---

@RW9 wrote:

I am not typing that text into SAS (write it as a datastep), so this is untested, but something like:

---

[LinusH]

It's a note, not an error or warning. It just tells you that it does as expected, matching the aggregate back on input records to do the filtering.

[AZIQ1]

Thank you, but it did not work. 

[RW9]

Could you elaborate on what did not work.  If you post test data in the form of a datastep, then I can test code before I post it.

[AZIQ1]

data have;
input str recordid Var1 Var2 ;
datalines;
1 7 1 1
1 8 0 1
2 8 4 1
1 10 7 8
2 10 3 9
3 10 2 10
run;

proc sql;
create table WANT as
select *
from HAVE
having count(RECORDID) = 3;
quit;

[RW9]

Ah yes, no grouping, so all counts were 6.  Try this, it subsets and groups on recordid, if the count of that is 3 at any point then output records with that countid:

data have;
input str recordid Var1 Var2 ;
datalines;
1 7 1 1
1 8 0 1
2 8 4 1
1 10 7 8
2 10 3 9
3 10 2 10
run;

proc sql;
  create table WANT as
  select *
  from HAVE
  where RECORDID in (select distinct RECORDID from HAVE group by RECORDID having count(*)=3);
quit;

[FreelanceReinh · Accepted solution]

Hi @AZIQ1,

Alternatively, you can use a data step:

data want;
do until(last.recordid);
  set have;
  by recordid;
  cnt=sum(cnt,1);
end;
do until(last.recordid);
  set have;
  by recordid;
  if cnt=3 then output;
end;
drop cnt;
run;

This won't give you any "remerging" notes in the log.

[AZIQ1]

Thank you Thank you - Best