topic Re: creating status from treatment date: longitudinal data in SAS Programming

creating status from treatment date: longitudinal data

Jest — Fri, 24 Mar 2017 16:31:00 GMT

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.

Re: creating status from treatment date: longitudinal data

collinelliot — Fri, 24 Mar 2017 17:05:32 GMT

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;

Re: creating status from treatment date: longitudinal data

ArtC — Fri, 24 Mar 2017 17:16:38 GMT

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;

Re: creating status from treatment date: longitudinal data

Jest — Fri, 24 Mar 2017 18:42:10 GMT

Thank you! it worked..

Re: creating status from treatment date: longitudinal data

Jest — Fri, 24 Mar 2017 18:42:28 GMT

thank you!

Re: creating status from treatment date: longitudinal data

mkeintz — Sat, 25 Mar 2017 02:19:11 GMT

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;

Re: creating status from treatment date: longitudinal data

Jest — Sun, 26 Mar 2017 19:29:12 GMT

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

Re: creating status from treatment date: longitudinal data

collinelliot — Sun, 26 Mar 2017 19:38:26 GMT

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.

Re: creating status from treatment date: longitudinal data

mkeintz — Sun, 26 Mar 2017 21:05:35 GMT

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;

Re: creating status from treatment date: longitudinal data

Jest — Sun, 26 Mar 2017 21:21:36 GMT

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

Re: creating status from treatment date: longitudinal data

Jest — Sun, 26 Mar 2017 22:13:46 GMT

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;

Re: creating status from treatment date: longitudinal data

mkeintz — Sun, 26 Mar 2017 22:35:02 GMT

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

Re: creating status from treatment date: longitudinal data

Jest — Sun, 26 Mar 2017 23:10:24 GMT

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

Re: creating status from treatment date: longitudinal data

collinelliot — Mon, 27 Mar 2017 13:21:21 GMT

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;

Re: creating status from treatment date: longitudinal data

Jest — Mon, 27 Mar 2017 16:38:53 GMT

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.