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
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.
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;
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
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:
Thank you, but it did not work.
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.
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;
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;
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.
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.
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.