DATA Step, Macro, Functions and more

How to create a new variable based on a series of variable?

Reply
Contributor
Posts: 38

How to create a new variable based on a series of variable?

I have a dataset like this:

idHospital_dtT1_dateT2_dateT3_dateT4_dateT5_date
13/4/20111/1/20113/2/30113/5/2011
21/1/20111/1/20112/1/2011
32/15/2011
410/1/20108/1/20109/1/2010

I want to create a new variable which will show the data of the first treatment AFTER hospital date. It will look like this:

idHospital_dtT1_dateT2_dateT3_dateT4_dateT5_datefirst_Treatment_dt
13/4/20111/1/20113/2/30113/5/20113/5/2011
21/1/20111/1/20112/1/20111/1/2011
32/15/2011
410/1/20108/1/20109/1/2010
Super User
Super User
Posts: 6,500

Re: How to create a new variable based on a series of variable?

How about using an array?

options ls=80;

data have;

  infile cards dlm='|' dsd truncover;

  input id  (Hospital_dt T1_date T2_date T3_date T4_date T5_date) (:mmddyy10.);

  format _all_ yymmdd10. id ;

cards;

1|3/4/2011|1/1/2011|3/2/3011|3/5/2011||

2|1/1/2011|1/1/2011|2/1/2011|||

3|2/15/2011|||||

4|10/1/2010|8/1/2010|9/1/2010|

run;

data want;

  set have ;

  array tdate t1_date t2_date t3_date t4_date t5_date ;

  do over tdate ;

    if tdate >= hospital_dt then first_treatment_dt = min(first_treatment_dt,tdate);

  end;

  format first_treatment_dt yymmdd10.;

run;

                                                                               f

                                                                               i

                                                                               r

                                                                               s

                                                                               t

                                                                               _

                                                                               t

             H                                                                 r

             o                                                                 e

             s                                                                 a

             p                                                                 t

             i          T          T          T          T          T          m

             t          1          2          3          4          5          e

             a          _          _          _          _          _          n

             l          d          d          d          d          d          t

O            _          a          a          a          a          a          _

b i          d          t          t          t          t          t          d

s d          t          e          e          e          e          e          t

1 1 2011-03-04 2011-01-01 3011-03-02 2011-03-05          .          . 2011-03-05

2 2 2011-01-01 2011-01-01 2011-02-01          .          .          . 2011-01-01

3 3 2011-02-15          .          .          .          .          .          .

4 4 2010-10-01 2010-08-01 2010-09-01          .          .          .          .

Contributor
Posts: 38

How to create a new variable based on a series of variable?

That's quick and neat.

One more question, if I know each location where the patience was treated. But I only need the first location AFTER hospital date:

idHospital_dtT1_dateT2_dateT3_dateT4_dateT5_dateLocation1Location2Location3first_Treatment_dtlocation
13/4/20111/1/20113/2/30113/5/2011abc3/5/2011c
21/1/20111/1/20112/1/2011bd1/1/2011b
32/15/2011
410/1/20108/1/20109/1/2010ab
Super Contributor
Posts: 1,636

How to create a new variable based on a series of variable?

you can add an array for locations to Art's code.

data want (drop=i);

  set have;

  array dates(*) T1_date--T5_date;

  array location(*) location1--location5;

  format first_Treatment_dt mmddyy10.;

  do i=1 to dim(dates);

    if dates(i) ge Hospital_dt then do;

      first_Treatment_dt=dates(i);

            first_location=location(i);

      leave;

    end;

  end;

run;

PROC Star
Posts: 7,363

How to create a new variable based on a series of variable?

Linlin, Agreed!  I was about to propose the exact same code, but didn't, as the results didn't come out as expected.  Then I realized that it wasn't working as expected because the second date, in the example data, was 3/2/3011.  Doubt if I'll be alive in 3011, but I couldn't figure out why 3/2 was being selected.

PROC Star
Posts: 7,363

Re: How to create a new variable based on a series of variable?

Same suggestion as Tom's, but I like to leave loops once a condition is met.  Conversely, if your data isn't in date order, then I'd definitely go with Tom's suggested code:

data want (drop=i);

  set have;

  array dates(*) T1_date--T5_date;

  format first_Treatment_dt mmddyy10.;

  do i=1 to dim(dates);

    if dates(i) ge Hospital_dt then do;

      first_Treatment_dt=dates(i);

      leave;

    end;

  end;

run;

Ask a Question
Discussion stats
  • 5 replies
  • 251 views
  • 0 likes
  • 4 in conversation