DATA Step, Macro, Functions and more

Macro for two series

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Macro for two series

Below is the SAS code I have been using to run a macro of the series of data "psych_date_i". I have another date "med_date_i" that I am needing to compare each of the psych_date_i readings to.

I am trying to see how many days there are between psych visits and med visits in my dataset. There are a total of 45 psych_date and 26 med_date. The macro below currently has to be run for each of the individual med_date_i dates.

I do not know if there another way to do what I am doing in less datasteps by writing another macro. I am new to using macros so I am still learning of the capabilities with them. Thank you for any help you can give,.

%macro med18;

%do i = 1 %to 45 ;

data med_psych&i;

set med_psych_visits;

num_day_m18_p&i = (med_date_18) - (psych_date_&i);

keep mrn num_day_m18_p&i;

run;

%end;

data med_18;

merge %do i=1 %to 45;

med_psych&i %end;;

run;

%mend;

%med18;


Accepted Solutions
Solution
‎05-14-2012 01:23 PM
Regular Contributor
Posts: 241

Re: Macro for two series

here is one way. hth

  data one;
    infile cards flowover;
    input patient (med1-med3 psy1-psy3) (:anydtdte.);
  cards;
  1 9/3/2010  .  .   9/4/2010  .  .
  2 9/3/2010  .  .    10/3/2010  .  .
  3 9/3/2010 9/5/2010  .  9/8/2010  .  .
  ;
  run;

  /* reshape to long */
  data meds psys;
    set one;
    array med

  • med1-med26;

  •     array psy
  • psy1-psy45;

  •     do i = 1 to dim(med);
          if missing(med) then continue;
          date=med;
          output meds;
        end;
        do i = 1 to dim(psy);
          if missing(psy) then continue;
          date=psy;
          output psys;
        end;
        format date date.;
        keep patient date;
      run;

      /* list psych visits that took place within a month, 
         in the future, of a medical visit */
      proc sql;
        create table two as
        select p.patient, m.date as med format=date., p.date as psy format=date.
        from   psys as p, meds as m
        where  p.patient = m.patient and
               (p.date between m.date and intnx("mon", m.date, 1, "s")-1)
        order by patient, m.date, p.date;
        /* check */
        select * from two;
      quit;
      title;
      /* on lst
      patient      med      psy
      --------------------------
           1  03SEP10  04SEP10
           3  03SEP10  08SEP10
           3  05SEP10  08SEP10

      */

    View solution in original post


    All Replies
    Frequent Contributor
    Posts: 101

    Re: Macro for two series

    If I understand your dataset then you should be able to do this in 1 data step vs. 46 using arrays. Try this:

    data med_18;

    set med_psych_visits;

    array num_day_m18_p(45) num_day_m18_p1-num_day_m18_p45;

    array psych_date_(45) psych_date_: ;

    do _i = 1 to 45;

         num_day_m18_p(_i) = med_date_18 - psych_date_(_i);

    end;

    keep mrn num_day_m18_p: ;

    run;

    Contributor
    Posts: 71

    Re: Macro for two series

    The only issue, is that I had 46 psych dates as well as 26 med visit dates. "m18" refers to the med visit number. Is there any way to apply an array to more than one variable?

    PROC Star
    Posts: 7,364

    Re: Macro for two series

    Arrays can be multidimensional as long as the data are of the same type (i.e., character or numeric).  You will probably get a better answer if you provide an example dataset and more explicit details regarding what you want to accomplish.

    Contributor
    Posts: 71

    Re: Macro for two series

    Thank you for the suggestion - I have attached a sample of some of the data.

    I am wanting to calculate the number of days between each medical and psych date in the data.

    Not everyone had 45 psych visits, but the most psych visits a patient(s) had was 45.

    Same applied to medical visits, the most medical visits a patient had in the time period was 26, but not everyone had 26 medical visits.

    I need to subtract each of the 45 psych visits against each of the 26 medical visits, for a total of 1170 calculations - end goal is to figure out which psych visits took place within a month of a medical visit. I have been trying to figure out the best way to accomplish this without creating over a thousand new variables, but I do not know of another way. I also do not know how to write the appropriate macro - I have not had to write any multidimensional macros before and appreciate any help or suggestions that can be provided. thank you for help.

    Frequent Contributor
    Posts: 101

    Re: Macro for two series

    Are you interested in psych visits within 30 days after a med visit? Or before? Or both?

    Contributor
    Posts: 71

    Re: Macro for two series

    Apologies - I am interested in 30 days after a med visit

    Frequent Contributor
    Posts: 101

    Re: Macro for two series

    I'm not sure what you want to happen after you identify the corresponding med and psych visits. In my example I capture each pair of dates in another set of variables.

    data visit30 (drop=_: med_date: psych_dateSmiley Happy;

    set sampledata;

    array medvis(*) med_date_1-med_date_26;

    array psyvis(*) psych_date_1-psych_date_45;

    array medpsypair(*) $20 med_psych_pair_1-med_psych_pair_45;

    do _m = 1 to dim( medvis ) until ( missing( medvis(_m) ));

       do _p = 1 to dim( psyvis ) until ( missing( psyvis(_p) ));

          if 0 <= psyvis(_p)-medvis(_m) <= 30 then do;

             _o = sum( _o, 1 );

             medpsypair(_o) = catx( ', ', put(medvis(_m),mmddyy8.), put(psyvis(_p),mmddyy8.) );

          end;

       end;

    end;

    run;

    Contributor
    Posts: 71

    Re: Macro for two series

    Thank you for your help. Both you and chang_y_chung had provided solutions that formatted the data different but output the data I needed. Thank you for help, the code work perfectly.

    Frequent Contributor
    Posts: 101

    Re: Macro for two series

    You need to be clearer about the structure of your dataset. Based on info you've provided thus far, each row/patient/case has psych_date_1-45, med_date_1-26, and you need to calculate the number of days between each psych date and med visit date?

    Solution
    ‎05-14-2012 01:23 PM
    Regular Contributor
    Posts: 241

    Re: Macro for two series

    here is one way. hth

      data one;
        infile cards flowover;
        input patient (med1-med3 psy1-psy3) (:anydtdte.);
      cards;
      1 9/3/2010  .  .   9/4/2010  .  .
      2 9/3/2010  .  .    10/3/2010  .  .
      3 9/3/2010 9/5/2010  .  9/8/2010  .  .
      ;
      run;

      /* reshape to long */
      data meds psys;
        set one;
        array med

  • med1-med26;

  •     array psy
  • psy1-psy45;

  •     do i = 1 to dim(med);
          if missing(med) then continue;
          date=med;
          output meds;
        end;
        do i = 1 to dim(psy);
          if missing(psy) then continue;
          date=psy;
          output psys;
        end;
        format date date.;
        keep patient date;
      run;

      /* list psych visits that took place within a month, 
         in the future, of a medical visit */
      proc sql;
        create table two as
        select p.patient, m.date as med format=date., p.date as psy format=date.
        from   psys as p, meds as m
        where  p.patient = m.patient and
               (p.date between m.date and intnx("mon", m.date, 1, "s")-1)
        order by patient, m.date, p.date;
        /* check */
        select * from two;
      quit;
      title;
      /* on lst
      patient      med      psy
      --------------------------
           1  03SEP10  04SEP10
           3  03SEP10  08SEP10
           3  05SEP10  08SEP10

      */

    Contributor
    Posts: 71

    Re: Macro for two series

    Thank you chang_y_chung. the code works perfectly and outputs the data in a format that is just what I was looking for. Thanks again.

    ☑ This topic is solved.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 11 replies
    • 450 views
    • 3 likes
    • 4 in conversation