Learning SAS? Welcome to the exclusive online community for all SAS learners.

Array/Multiple Records Issue

New Contributor
Posts: 2

Array/Multiple Records Issue

I'm trying to deal with multiple records per patient, making it so there is only one record per patient. I have an array for each variable, but the code is only working for two of the variables (missed_dose_amount and missed_dose_calc) not missed_dose_date. There seems to be an issue with the date variable. For the date, it only shows the last date for each patient, the dates that come before the last date are 'missing'. Does anyone have any idea why this would be happening? THANKS!


proc sort data=missed_doses_11febA;

by pat_id missed_dose_date;


data vector (keep=pat_id missed_dose_date1-missed_dose_date30 missed_dose_amount1-missed_dose_amount30 missed_dose_calc1-missed_dose_calc30);

set missed_doses_11febA;

by pat_id;

retain i missed_dose_date1-missed_dose_date30 missed_dose_amount1-missed_dose_amount30 missed_dose_calc1-missed_dose_calc30;

array dt {*} missed_dose_date1-missed_dose_date30;

array amt {*} missed_dose_amount1-missed_dose_amount30;

array cal {*} missed_dose_calc1-missed_dose_calc30;

if first.pat_id then do j = 1 to 30;

dt {j} = .;

amt {j} = .;

cal {j} = .;


if first.pat_id then i=0;


if missed_dose_date ne . then do;

dt {i} = missed_dose_date;

amt {i} = missed_dose_amount;

cal {i} = missed_dose_calc;


if last.pat_id then output;

format missed_dose_date1-missed_dose_date30 mmddyy10.;


Screen Shot 2015-02-13 at 10.52.33 AM.png

Super User
Posts: 17,784

Re: Array/Multiple Records Issue

Can you post you log please?

New Contributor
Posts: 2

Re: Array/Multiple Records Issue

1027  data vector (keep=pat_id missed_dose_date1-missed_dose_date30

1027! missed_dose_amount1-missed_dose_amount30 missed_dose_calc1-missed_dose_calc30);

1028  set missed_doses_13febA;

1029  by pat_id;

1030  /*length missed_dose_date1-missed_dose_date30 $ 8;*/

1031  retain i missed_dose_date1-missed_dose_date30 missed_dose_amount1-missed_dose_amount30

1031! missed_dose_calc1-missed_dose_calc30;

1032  array dt {*} missed_dose_date1-missed_dose_date30;

1033  array amt {*} missed_dose_amount1-missed_dose_amount30;

1034  array cal {*} missed_dose_calc1-missed_dose_calc30;

1035  if first.pat_id then do j = 1 to 30;

1036  dt {j} = .;

1037  amt {j} = .;

1038  cal {j} = .;

1039  end;

1040  if first.pat_id then i=0;

1041  i+1;

1042  if missed_dose_amount ne . then do; /*THIS IS NOT THE PROBLEM*/

1043  dt {i} = missed_dose_date;

1044  amt {i} = missed_dose_amount;

1045  cal {i} = missed_dose_calc;

1046  if last.pat_id then output;

1047  end;

1048  format missed_dose_date1-missed_dose_date30 mmddyy10.;

1049  run;

NOTE: There were 303 observations read from the data set WORK.MISSED_DOSES_13FEBA.

NOTE: The data set WORK.VECTOR has 63 observations and 91 variables.

Super User
Super User
Posts: 6,498

Re: Array/Multiple Records Issue

Do any of the RETAINED variables already exist in the input data set?  If so then they will overwrite the values you generated using your arrays.

Super User
Posts: 17,784

Re: Array/Multiple Records Issue

proc sort data=missed_doses_11febA;

by pat_id missed_dose_date;


You sort by missed_dose_date so any missing end up first in your records rather than last. Try sorting by descending instead?

proc sort data=missed_doses_11febA;

by pat_id descending missed_dose_date;


Posts: 52

Re: Array/Multiple Records Issue

Since you have not shown the input data, I made up some of my own, using fewer variables and shorter variable names to make things clear.

data t_have;
input id zDate zAmt zCal;
format zDate mmddyy10.;
10 2000 1.5 3.1
10   .   .   .
10 2010 2.1 1.4
20   .   .   .
20 2200 9.2 1.1
20   .   .   .

I modified slightly your algorithm, without changing the heart of the logic.

proc sort data=t_have;
by id zDate;

data t_want (keep=id zDate1-zDate3 zAmt1-zAmt3 zCal1-zCal3);
   set t_have;
by id;

retain i zDate1-zDate3 zAmt1-zAmt3 zCal1-zCal3;

  array dt {*} zDate1-zDate3;
  array amt {*} zAmt1-zAmt3;
  array cal {*} zCal1-zCal3;

if first.id then do;
    call missing(of dt(*));
    call missing(of amt(*));
    call missing(of cal(*));

if zDate ne . then do;
   dt {i} = zDate;
  amt {i} = zAmt;
  cal {i} = zCal;

if last.id then output;

format zDate1-zDate3 mmddyy10.;

Table t_have is:
id   zDate1   zDate2      zDate3    zAmt1   zAmt2   zAmt3   zCal1   zCal2   zCal3
10   .        6/23/1965   7/3/1965    .      1.5     2.1      .      3.1     1.4
20   .         .          1/9/1966    .       .      9.2      .       .      1.1

From my POV, I see nothing amiss unless there is something particular about the input data you are using.

Ask a Question
Discussion stats
  • 5 replies
  • 4 in conversation