Hello everyone.
I have a data of the form:
id exercice date of check records
1 1 07/12/2013 12.3
1 1 23/01/2014 15
1 1 03/03/2014 15
1 1 23/04/2014 13.5
1 2 15/08/2014 .
1 2 18/10/2014 17
1 2 21/11/2014 .
1 2 07/01/2015 17
1 2 01/03/2015 16.5
2 1 03/03/2014 14
2 1 23/04/2014 13.5
2 1 15/08/2014 18
2 1 18/10/2014 17
2 1 27/12/2014 17.5
2 3 26/02/2013 .
2 3 15/04/2013 17
2 3 14/06/2013 14.5
2 3 02/08/2013 15
3 1 28/08/2013 16
3 1 30/10/2013 16
3 1 5/02/2014 14.5
3 1 11/03/2014 15
3 1 23/04/2014 .
3 1 13/06/2014 .
I try to edit this database according to the number of successive checks carried out for each Id with several exercises.
The goal is to keep only observations of the Ids having at least 3 successive checks from the beginning of the exercise. If the Id fails the first check during a given exercise it will be eliminated even if it has to perform all the other checks.
The count of successive checks stops against a missing value and only the counted data will be maintained.
In the example given only the bold-red observations will be maintained.
Thank you for giving me your time.
Yes you understood me well, and you answered my condition 2. condition 1 was not to display the observations below a certain number n. (In my case n = 3).
So I added a proc sql to count the number of observations generated by your code for each id in each exercise and I think it works fine. Or do you have another proposal?
data temp; set have; flag=not missing(records); run; data temp1; set temp; by id flag notsorted; group+first.flag; run; data want0; set temp1(where=(flag=1)); by id group; if first.id then count=0; count+first.group; if count=1; run; proc sql; create table want1 as select ID,exercice, date_of_check,records , count(distinct records ) as counter from want0 group by Id, exercice; quit; data want;set want1; if counter<2 then delete; drop counter; run;
I think you are trying to do something like the following:
data have; input id exercice date_of_check :ddmmyy10. records; format date_of_check date9.; cards; 1 1 07/12/2013 12.3 1 1 23/01/2014 15 1 1 03/03/2014 15 1 1 23/04/2014 13.5 1 2 15/08/2014 . 1 2 18/10/2014 17 1 2 21/11/2014 . 1 2 07/01/2015 17 1 2 01/03/2015 16.5 2 1 03/03/2014 14 2 1 23/04/2014 13.5 2 1 15/08/2014 18 2 1 18/10/2014 17 2 1 27/12/2014 17.5 2 3 26/02/2013 . 2 3 15/04/2013 17 2 3 14/06/2013 14.5 2 3 02/08/2013 15 3 1 28/08/2013 16 3 1 30/10/2013 16 3 1 5/02/2014 14.5 3 1 11/03/2014 15 3 1 23/04/2014 . 3 1 13/06/2014 . ; data want; do until (last.exercice); set have; by id exercice; if first.exercice then do; counter=1; if missing(records) then counter=0; end; else if 1 le counter le 3 then do; if missing(records) then counter=0; else counter+1; end; end; do until (last.exercice); set have; by id exercice; if counter eq 4 then output; end; run;
Art, CEO, AnalystFinder.com
Hi,
Thank you very much for helping me. For the principle this is what I am looking for, except that beyond the first 3 observations without missing values, the counter must stop as soon as there is a missing value, and the output displays the results.
Take the example of Id = 3, Exercise 1. the condition of the first 3 records with no missing values is well filled, but the counter did not stop when there was a missing value on the date 23/04/2014 And the output contains a missing value on the corresponding date.
I'm not too accustomed to editing the data that's why I ask dumb questions can be. Thank you for your passion.
Honestly, Your question is hard to understand.
Assuming I know your question and using @art297 's data .
data have;
input id exercice date_of_check :ddmmyy10. records;
format date_of_check date9.;
cards;
1 1 07/12/2013 12.3
1 1 23/01/2014 15
1 1 03/03/2014 15
1 1 23/04/2014 13.5
1 2 15/08/2014 .
1 2 18/10/2014 17
1 2 21/11/2014 .
1 2 07/01/2015 17
1 2 01/03/2015 16.5
2 1 03/03/2014 14
2 1 23/04/2014 13.5
2 1 15/08/2014 18
2 1 18/10/2014 17
2 1 27/12/2014 17.5
2 3 26/02/2013 .
2 3 15/04/2013 17
2 3 14/06/2013 14.5
2 3 02/08/2013 15
3 1 28/08/2013 16
3 1 30/10/2013 16
3 1 5/02/2014 14.5
3 1 11/03/2014 15
3 1 23/04/2014 .
3 1 13/06/2014 .
;
run;
data temp;
set have;
flag=not missing(records);
run;
data temp1;
set temp;
by id flag notsorted;
group+first.flag;
run;
data want;
set temp1(where=(flag=1));
by id group;
if first.id then count=0;
count+first.group;
if count=1;
drop flag group count;
run;
Yes you understood me well, and you answered my condition 2. condition 1 was not to display the observations below a certain number n. (In my case n = 3).
So I added a proc sql to count the number of observations generated by your code for each id in each exercise and I think it works fine. Or do you have another proposal?
data temp; set have; flag=not missing(records); run; data temp1; set temp; by id flag notsorted; group+first.flag; run; data want0; set temp1(where=(flag=1)); by id group; if first.id then count=0; count+first.group; if count=1; run; proc sql; create table want1 as select ID,exercice, date_of_check,records , count(distinct records ) as counter from want0 group by Id, exercice; quit; data want;set want1; if counter<2 then delete; drop counter; run;
You can combine the last SQL and data step into one SQL.
proc sql; create table want1 as select ID,exercice, date_of_check,records from want0 group by Id, exercice
having count(distinct records) < 2; quit;
Yes, it's true, I do not know her. Thank you very much for your help.
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.