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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 2026 views
  • 4 likes
  • 5 in conversation