topic Re: Create subset by enumeration variable in SAS Data Management

Create subset by enumeration variable

AZIQ1 — Tue, 29 Mar 2016 19:15:17 GMT

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

Re: Create subset by enumeration variable

RW9 — Tue, 29 Mar 2016 19:21:56 GMT

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;

Re: Create subset by enumeration variable

AZIQ1 — Tue, 29 Mar 2016 19:29:05 GMT

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

Re: Create subset by enumeration variable

RW9 — Tue, 29 Mar 2016 19:35:38 GMT

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

Re: Create subset by enumeration variable

LinusH — Tue, 29 Mar 2016 19:36:12 GMT

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.

Re: Create subset by enumeration variable

AZIQ1 — Tue, 29 Mar 2016 19:43:34 GMT

Thank you, but it did not work.

Re: Create subset by enumeration variable

RW9 — Tue, 29 Mar 2016 19:45:12 GMT

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.

Re: Create subset by enumeration variable

AZIQ1 — Tue, 29 Mar 2016 19:47:46 GMT

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;

Re: Create subset by enumeration variable

FreelanceReinh — Tue, 29 Mar 2016 19:50:12 GMT

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.

Re: Create subset by enumeration variable

RW9 — Tue, 29 Mar 2016 19:53:57 GMT

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;

Re: Create subset by enumeration variable

AZIQ1 — Tue, 29 Mar 2016 19:57:31 GMT

Thank you Thank you - Best

Re: Create subset by enumeration variable

data_null__ — Tue, 29 Mar 2016 20:20:49 GMT

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: