creating status from treatment date: longitudinal data

Reply
Contributor
Posts: 21

creating status from treatment date: longitudinal data

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.

 

 

Super Contributor
Posts: 284

Re: creating status from treatment date: longitudinal data

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

Re: creating status from treatment date: longitudinal data

thank you!

Valued Guide
Posts: 632

Re: creating status from treatment date: longitudinal data

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

Re: creating status from treatment date: longitudinal data

Thank you! it worked..

Super User
Posts: 789

Re: creating status from treatment date: longitudinal data

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;

Contributor
Posts: 21

Re: creating status from treatment date: longitudinal data

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

 

 

Super Contributor
Posts: 284

Re: creating status from treatment date: longitudinal data

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. 

Contributor
Posts: 21

Re: creating status from treatment date: longitudinal data

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;

Super User
Posts: 789

Re: creating status from treatment date: longitudinal data

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;

Contributor
Posts: 21

Re: creating status from treatment date: longitudinal data

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

 

Super User
Posts: 789

Re: creating status from treatment date: longitudinal data

"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.

Contributor
Posts: 21

Re: creating status from treatment date: longitudinal data

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

 

Super Contributor
Posts: 284

Re: creating status from treatment date: longitudinal data

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

Re: creating status from treatment date: longitudinal data

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.

 

 

 

Ask a Question
Discussion stats
  • 14 replies
  • 205 views
  • 1 like
  • 4 in conversation