BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AZIQ1
Quartz | Level 8

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8
The log says:
NOTE: The query requires remerging summary statistics back with the original data
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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__
Jade | Level 19

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
Tourmaline | Level 20
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
AZIQ1
Quartz | Level 8

Thank you, but it did not work. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Jade | Level 19

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
Quartz | Level 8
Thank you Thank you - Best

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 11 replies
  • 2813 views
  • 4 likes
  • 5 in conversation