BookmarkSubscribeRSS Feed
Jest
Obsidian | Level 7

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.

 

 

14 REPLIES 14
collinelliot
Barite | Level 11

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;
ArtC
Rhodochrosite | Level 12

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
Obsidian | Level 7

Thank you! it worked..

mkeintz
PROC Star

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;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Jest
Obsidian | Level 7

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
Barite | Level 11

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
Obsidian | Level 7

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
PROC Star

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;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Jest
Obsidian | Level 7

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
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Jest
Obsidian | Level 7

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
Barite | Level 11

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
Obsidian | Level 7

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.

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 837 views
  • 1 like
  • 4 in conversation