SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dinurik
Fluorite | Level 6

Hi SAS Programmers,

 

Could you please help me with this problem.

Here is my data:

    data mydata;
        input id time var1 var2 @@;
        cards;
        1 1 3 1
        1 2 3 1
        1 3 3 1
        1 4 2 1
        1 5 . .
        2 1 3 1
        2 2 . .
        2 3 . .
        2 4 2 0
        2 5 . .
        3 1 2 0
        3 2 2 0
        3 3 2 1
        3 4 . .
        3 5 . .
        ;
    run;

I need to create a variable for censoring, so that it takes a value of 1 if a person dropped out of the study, i.e. if persons variables 1 and 2 are both missing from now on. Here is what I need to get:

   id   time   var1  var2 cens 
11310
12310
13310
14210
15..1
21310
22..0
23..0
24200
25..1
31200
32200
33210
34..1
35..1

 

I cannot base the calculation of "cens" on missing values, because there is intermittent missingness, so the the code: "if var1=. and var2=. then cens=1" will produce positive censoring indicator for id 2 at times 2 and 3, which is not correct. I feel like the right code will need some sorting and retain statement, but can't figure it out by myself.  

 

I would appreciate any tips or advice!

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

HI @Dinurik It seems pretty straight forward look up problem in my opinion-->Using Hash

data mydata;
input id time var1 var2 @@;
cards;
1 1 3 1
1 2 3 1
1 3 3 1
1 4 2 1
1 5 . .
2 1 3 1
2 2 . .
2 3 . .
2 4 2 0
2 5 . .
3 1 2 0
3 2 2 0
3 3 2 1
3 4 . .
3 5 . .
;
run;


data want;
 if _n_=1 then do;
  dcl hash H () ;
  h.definekey ("time") ;
  h.definedone () ;
 end;
 do _n_=1 by 1 until(last.id);
  set mydata;
  by id ;
  if nmiss(var1,var2)=2 then h.add();
  else h.clear();
 end;
 do _n_=1 to _n_;
  set mydata;
  cens=h.find()=0;
  output;
 end;
 h.clear();
run;

proc print noobs;run;

results.PNG

 






View solution in original post

6 REPLIES 6
novinosrin
Tourmaline | Level 20

HI @Dinurik It seems pretty straight forward look up problem in my opinion->Using Array-Key indexing

data mydata;
input id time var1 var2 @@;
cards;
1 1 3 1
1 2 3 1
1 3 3 1
1 4 2 1
1 5 . .
2 1 3 1
2 2 . .
2 3 . .
2 4 2 0
2 5 . .
3 1 2 0
3 2 2 0
3 3 2 1
3 4 . .
3 5 . .
;
run;

data want;
 do _n_=1 by 1 until(last.id);
  set mydata;
  by id ;
  array t(9999)	_temporary_;
  if nmiss(var1,var2)=2 then t(time)=time;
  else call missing(of t(*));
 end;
 do _n_=1 to _n_;
  set mydata;
  cens=^^t(time);
  output;
 end;
 call missing(of t(*));
run;
proc print noobs;run;

results.PNG

 

novinosrin
Tourmaline | Level 20

HI @Dinurik It seems pretty straight forward look up problem in my opinion-->Using Hash

data mydata;
input id time var1 var2 @@;
cards;
1 1 3 1
1 2 3 1
1 3 3 1
1 4 2 1
1 5 . .
2 1 3 1
2 2 . .
2 3 . .
2 4 2 0
2 5 . .
3 1 2 0
3 2 2 0
3 3 2 1
3 4 . .
3 5 . .
;
run;


data want;
 if _n_=1 then do;
  dcl hash H () ;
  h.definekey ("time") ;
  h.definedone () ;
 end;
 do _n_=1 by 1 until(last.id);
  set mydata;
  by id ;
  if nmiss(var1,var2)=2 then h.add();
  else h.clear();
 end;
 do _n_=1 to _n_;
  set mydata;
  cens=h.find()=0;
  output;
 end;
 h.clear();
run;

proc print noobs;run;

results.PNG

 






Dinurik
Fluorite | Level 6

Thank you so much! It is a huge help. It would take me a couple of days if not more to figure it out on my own. It worked!

Ksharp
Super User
data mydata;
input id time var1 var2 @@;
cards;
1 1 3 1
1 2 3 1
1 3 3 1
1 4 2 1
1 5 . .
2 1 3 1
2 2 . .
2 3 . .
2 4 2 0
2 5 . .
3 1 2 0
3 2 2 0
3 3 2 1
3 4 . .
3 5 . .
;
run;
data want;
 do i=1 by 1 until(last.id);
  set mydata;
  by id;
  if not missing(var1) or not missing(var2) then _i=i;
 end;

 do i=1 by 1 until(last.id);
  set mydata;
  by id;
  flag= i>_i ;output;
 end;
 drop i _i;
run;
novinosrin
Tourmaline | Level 20

@Ksharp  Xia, do you remember I often utter my mother is a huge fan of yours. She often bets you are the champ. You never cease to inspire me to go the extra mile.

Anyways, some more fun.

 


data mydata;
        input id time var1 var2 @@;
        cards;
        1 1 3 1
        1 2 3 1
        1 3 3 1
        1 4 2 1
        1 5 . .
        2 1 3 1
        2 2 . .
        2 3 . .
        2 4 2 0
        2 5 . .
        3 1 2 0
        3 2 2 0
        3 3 2 1
        3 4 . .
        3 5 . .
        ;
    run;

proc sql;
create table want as
select *, time> max(time*(n(var1,var2)>0)) as cens
from mydata
group by id
order by id, time;
quit;
id time var1 var2 cens
1 1 3 1 0
1 2 3 1 0
1 3 3 1 0
1 4 2 1 0
1 5 . . 1
2 1 3 1 0
2 2 . . 0
2 3 . . 0
2 4 2 0 0
2 5 . . 1
3 1 2 0 0
3 2 2 0 0
3 3 2 1 0
3 4 . . 1
3 5 . . 1
Dinurik
Fluorite | Level 6

This is awesome. I checked with my actual data, it works great. Thank you! 

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1396 views
  • 1 like
  • 3 in conversation