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.
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;
thank you!
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;
Thank you! it worked..
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;
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
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.
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;
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;
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
"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.
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
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.