topic Re: Sorting of data according to a criterion in SAS Data Management

Sorting of data according to a criterion

soumri — Fri, 14 Jul 2017 00:44:23 GMT

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.

Re: Sorting of data according to a criterion

art297 — Fri, 14 Jul 2017 00:56:08 GMT

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

Re: Sorting of data according to a criterion

soumri — Fri, 14 Jul 2017 08:26:46 GMT

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.

Re: Sorting of data according to a criterion

Ksharp — Fri, 14 Jul 2017 09:31:22 GMT

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;

Re: Sorting of data according to a criterion

soumri — Fri, 14 Jul 2017 10:59:06 GMT

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;

Re: Sorting of data according to a criterion

Ksharp — Sat, 15 Jul 2017 11:51:32 GMT

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;

Re: Sorting of data according to a criterion

soumri — Sat, 15 Jul 2017 13:46:48 GMT

Yes, it's true, I do not know her. Thank you very much for your help.