SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Sorting of data according to a criterion

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

Sorting of data according to a criterion

[ Edited ]

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.

 

 


Accepted Solutions
Solution
‎07-14-2017 08:42 AM
Contributor
Posts: 65

Re: Sorting of data according to a criterion

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;

View solution in original post


All Replies
PROC Star
Posts: 7,492

Re: Sorting of data according to a criterion

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

Contributor
Posts: 65

Re: Sorting of data according to a criterion

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.

Super User
Posts: 10,044

Re: Sorting of data according to a criterion

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;
Solution
‎07-14-2017 08:42 AM
Contributor
Posts: 65

Re: Sorting of data according to a criterion

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;
Super User
Posts: 10,044

Re: Sorting of data according to a criterion

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;
Contributor
Posts: 65

Re: Sorting of data according to a criterion

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 233 views
  • 3 likes
  • 3 in conversation