SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Create subset by enumeration variable

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Create subset by enumeration variable

[ Edited ]

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

 

 


Accepted Solutions
Solution
‎03-29-2016 03:57 PM
Trusted Advisor
Posts: 1,117

Re: Create subset by enumeration variable

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.

View solution in original post


All Replies
Super User
Super User
Posts: 7,955

Re: Create subset by enumeration variable

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;
Contributor
Posts: 71

Re: Create subset by enumeration variable

The log says:
NOTE: The query requires remerging summary statistics back with the original data
Super User
Super User
Posts: 7,955

Re: Create subset by enumeration variable

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

Respected Advisor
Posts: 3,799

Re: Create subset by enumeration variable

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:


 

Super User
Posts: 5,429

Re: Create subset by enumeration variable

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.
Data never sleeps
Contributor
Posts: 71

Re: Create subset by enumeration variable

Thank you, but it did not work. 

Super User
Super User
Posts: 7,955

Re: Create subset by enumeration variable

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.

Contributor
Posts: 71

Re: Create subset by enumeration variable

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;

Super User
Super User
Posts: 7,955

Re: Create subset by enumeration variable

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;
Solution
‎03-29-2016 03:57 PM
Trusted Advisor
Posts: 1,117

Re: Create subset by enumeration variable

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.

Contributor
Posts: 71

Re: Create subset by enumeration variable

Posted in reply to FreelanceReinhard
Thank you Thank you - Best
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 867 views
  • 3 likes
  • 5 in conversation