creating status from treatment date: longitudinal data

[Jest]

Hi,

I am trying to create a status variable (0, 1) from treatment date (TXdate).

TXdate is a character variable that looks like this 10-MAR-2010....

This is how the data looks:

ID           TXdate                time

1                                           0

1                                           1

1                                           2

1                                           3

1                                           4

2                                           0

2                                           1

2         10-MAR-2010            2

2                                           3

2                                           4

so on and so forth for 900 IDs or so.

So ID1 had no Txdate, status will be 0, where as ID2 had at some point a TXdate status will be 1.

How the data should look

ID           TXdate                time           Status

1                                           0                 0

1                                           1                 0

1                                           2                 0

1                                           3                 0 

1                                           4                 0

2                                           0                 1

2                                           1                 1

2         10-MAR-2010            2                 1

2                                           3                  1

2                                           4                  1

How do I treat the date variable here? Do I tell SAS if Txdate= " " then status=0; else if Txdate not missing (how do I tell it to look at not missing) then status=1?

Any thoughts? Thanks.

[collinelliot]

I think you can do what you want with the following:

proc sql;
   CREATE TABLE want AS
   SELECT *,
                  max(not missing(txDate)) AS status
   FROM have
   GROUP BY id;
quit;

[Jest]

thank you!

[ArtC]

You can save those IDs with a treatment date and then merge them back to the original data.

data have;
input id @4 txdate $11. @17 time;
datalines;
1               0
1               1
1               2
1               3
1               4
2               0
2               1
2  10-MAR-2010  2
2  11-mar-2010  3
2               4
run;
proc sort data=have;
   by id;
   run;
data withdate(keep=id);
   set have;
   if txdate ne ' ';
   run;
data want;
   merge have withdate(in=inwith);
   by id;
   status=inwith;
   run;
proc print data=want;
   run;

[Jest]

Thank you! it worked..

[mkeintz]

The data appear to be sorted by ID, making this a good case for a SET statement having a data set name specified twice.

The first SET argument reads only cases for txdate not blank, and the second reads all cases - (so the non-blanks are read twice - once at the begining of each by group and once in original order).  Therefore at the beginning of each by group you can establish the STATUS value:

data want;

  set  have (where=(txdate^=' '))   have (in=inkeep);

  by id;

  retain status;

  if first.id then status=ifn(txdate^=' ',1,0);

  if inkeep;

run;

[Jest]

Thank you!

Quick question...Can I assign a 1 to the status variable as soon as it hits a Txdate?

This time instead of an ID being 1 if there is TXdate. The status variable only turns to 1 once it hits a Txdate at a certain time point...

Can I make the data look like this:

ID          TXdate              Time         status

1                                        0               0

1                                        1               0

1           MAR102016         2               1

1                                        3               1

1                                        4               1

[collinelliot]

That's easily done with a retain statement, but I am limited to my phone until tomorrow. Someone else here will surely provide you an example. 

[Jest]

Thank you. I tried the following, but it doesn't seem to work. any ideas? 

data want (drop=get)

set have;

by ID;

retain get;

if first.id then get=0;

status=0;

if get eq 0 and TXdate^= ' ' then do;

status=1;

get=1;

end;

run;

[mkeintz]

Yes, but you have to not only RETAIN the status variable, but remember to reset it to 0 when starting a new by group:

data want;

  set have;

  by id;

  retain status;

  if first.id then status=0;

  if txdate^=' ' then status=1;

run;

[Jest]

Thank you...However that didn't work

I am trying the following:

data want (drop=get)

set have;

by ID;

retain get;

if first.id then get=0;

status=0;

if get eq 0 and TXdate^= ' ' then do;

status=1;

get=1;

end;

run;

This also isn't working. Any ideas, of what I could be doing wrong

[mkeintz]

"didn't work" is like telling your doctor "I don't feel well".

Show the (1) log, and (2) results.  Then a diagnosis and possibly a prescription can be made.

[Jest]

Thanks.. sure. No errors in the log...The results is just a status variable with all 0 inputs..

So this how I am trying to make the data look for the status variable:

ID         TXdate                     Time           Status

1                                              0                  0

1                                              1                  0

1                                              2                  0

1                                              3                  0

1                                              4                  0

2                                              0                  0

2                                              1                  0

2             MAR122010             2                  1

2                                              3                  1

2                                              4                  1

3                                              0                  0

3             MAY092012              1                  1

3                                              2                  1 

3                                              3                  1

Thanks

[collinelliot]

You're missing a ; on your data statement, but it looks like you're doing a number of unnecessary steps. Will the following not provide what you want?

data have;
input id @4 txdate $11. @17 time;
datalines;
1               0
1               1
1               2
1               3
1               4
2               0
2               1
2  10-MAR-2010  2
2  11-mar-2010  3
2               4
;

data want;
    retain status;
    set have;
    by id;
    if first.id then status = 0;
    else if not missing(txdate) then status = 1;
run;

[Jest]

thank you. Sorry, I thought I updated the group. It worked out in the end.I agree that there were some unnecessary steps in my code.