DATA Step, Macro, Functions and more

How to set subsequent values to missing and keep first occurence

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

How to set subsequent values to missing and keep first occurence

Hi I am trying to keep the first occurence of a variable and set the subsequent values of the variables to missing

My data looks like this

 

Data have;

input  id time AMI;

datalines;

 1 1  0

 1 2  0

 1 3  1

 1 4  0

 2  1  1

 2  2  0

 2  2  1

 2  4  0

run;

 

I would like my data to look like this

ID time AMI

 1 1  0

 1 2  0

 1 3  1

 1 4  .

 2  1  1

 2  2  .

 2  2  .

 2  4  .

 

Basically, I only want to assess the first occurence of AMI

 

Thank you


Accepted Solutions
Solution
‎10-10-2016 09:55 PM
Trusted Advisor
Posts: 1,378

Re: How to set subsequent values to missing and keep first occurence

Assuming data is sorted by ID you can do:

 

data want;

 set have;

  by ID;

       retain flag; drop flag;

 

      if first.id then flag=0;

      if flag=1 then ami=.;

      if ami=1 and flag=0 then flag=1;

run;

View solution in original post


All Replies
Solution
‎10-10-2016 09:55 PM
Trusted Advisor
Posts: 1,378

Re: How to set subsequent values to missing and keep first occurence

Assuming data is sorted by ID you can do:

 

data want;

 set have;

  by ID;

       retain flag; drop flag;

 

      if first.id then flag=0;

      if flag=1 then ami=.;

      if ami=1 and flag=0 then flag=1;

run;

Occasional Contributor
Posts: 14

Re: How to set subsequent values to missing and keep first occurence

Thank you very Shmuel. It works perfectly

Best

Respected Advisor
Posts: 4,649

Re: How to set subsequent values to missing and keep first occurence

I would do:

 

data want;
do until(last.id);
    set have; by id;
    if AMImet 
        then call missing(newAMI);
        else newAMI = AMI;
    output;
    if AMI then AMImet = 1;
    end;
drop AMI AMImet; rename newAMI=AMI;
run;
PG
Occasional Contributor
Posts: 14

Re: How to set subsequent values to missing and keep first occurence

Thank you very PGStats. Your codes works awesome too.

Thanks so much

Best

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 282 views
  • 1 like
  • 3 in conversation