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
Frequent Contributor
Posts: 81
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,158

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: 9,402

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

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: 9,402

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,845

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,849

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
Frequent Contributor
Posts: 81

Re: Create subset by enumeration variable

Thank you, but it did not work. 

Super User
Super User
Posts: 9,402

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.

Frequent Contributor
Posts: 81

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: 9,402

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,158

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.

Frequent Contributor
Posts: 81

Re: Create subset by enumeration variable

Posted in reply to FreelanceReinhard
Thank you Thank you - Best
🔒 This topic is solved and locked.

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

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