BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
soumri
Quartz | Level 8

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
soumri
Quartz | Level 8

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

6 REPLIES 6
art297
Opal | Level 21

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

soumri
Quartz | Level 8

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.

Ksharp
Super User

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;
soumri
Quartz | Level 8

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;
Ksharp
Super User

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;
soumri
Quartz | Level 8

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

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
  • 6 replies
  • 1080 views
  • 3 likes
  • 3 in conversation