Correcting Dose Order

[jcis7]

G'day.

I have a large file (>5000 obs). Each child has immunization doses for they childhood vaccines they received.

I inherited a data checking program that identifies which doses in the dose series (i.e, 3 Hepatitis B Doses required) are out of order.

How do I get the dates in order (earlier date first, later date next, and latest date last:

school          childnum                hepb1                hepb2                hepb3

P001                    001            01/25/2008          10/24/2007         07/25/2008    (this should be hepb1:  10/24/2007, hepb2:  01/25/2008, hepb3:  07/25/2008)

P001                    002             10/11/2008          07/10/2008        04/10/2007  (this should be: hepb1:  04/10/2007, hepb2:  07/10/2008, hepb3:  10/11/2008)

P002                    001            11/15/2007            05/15/2008        02/11/2008  (this should be: hepb1:  as is, hepb2:  02/11/2008, hepb3:  05/15/2008)

etc...

Any help you can give is much appreciated.

Thanks!

[PGStats]

Use call routine SORTN :

data have;

input school $ childnum (hepb1 hepb2 hepb3) (:mmddyy10.);

format hepb1-hepb3 yymmdd.;

datalines;

P001 001 01/25/2008 10/24/2007 07/25/2008

P001 002 10/11/2008 07/10/2008 04/10/2007

P002 001 11/15/2007 05/15/2008 02/11/2008

;

data want;

set have;

call sortn(of hepb1-hepb3);

run;

proc print data=want noobs; run;

PG

[jcis7]

This works except I have blank values for some vaccines. For example if the last dose of Hepb is blank, it places the blank value first.  How do you sort while keeping any blank values last (i.e, if hepb3 is blank, for it to sort with hepb3 as being blank. Right now, it's sorting with hepb1 as blank).

For some children, they're missing 2 doses of hepb.  Thanks.

[Haikuo]

A little tweak on PG's code should do:

data have;

input school $ childnum (hepb1 hepb2 hepb3) (:mmddyy10.);

format hepb1-hepb3 yymmdd.;

datalines;

P001 001 01/25/2008 10/24/2007 07/25/2008

P001 002 . 07/10/2008 04/10/2007

P002 001 11/15/2007 . 02/11/2008

;

data want;

set have;

array h hepb:;

retain _d;

if _n_=1 then _d=dim(h);

do i=1 to _d;

  h(i)=coalesce(h(i),'31dec9999'd);

end;

call sortn(of h(*));

if h(_d) = '31dec9999'd then call missing (h(_d));

drop i _d;

run;

proc print data=want noobs; run;

Regards,

Haikuo

[jcis7]

Appreciate your help; this sorts the dates in order for keeps the 31Dec9999 in the blank fields. How do I drop the 31Dec9999? 

[Haikuo]

?

[PGStats · Accepted solution]

Another tweak, in which the dates are simply shifted down the array:

data have;

input school $ childnum (hepb1 hepb2 hepb3) (:mmddyy10.);

format hepb1-hepb3 yymmdd.;

datalines;

P001 001 01/25/2008 10/24/2007 07/25/2008

P001 002 10/11/2008 07/10/2008 04/10/2007

P002 001 11/15/2007 05/15/2008 02/11/2008

P002 003 05/15/2008 . 02/11/2008

P002 004 . . 02/11/2008

;

data want;

set have;

array h{*} hepb:;

call sortn(of h{*});

n = nmiss(of h{*});

do i = 1 to dim(h)-n;

    h{i} = h{i+n};

    end;

do i = 1 to n;

    call missing(h{dim(h)-i+1});

    end;

drop i n;

run;

proc print data=want noobs; run;

PG

[jcis7]

I lost my code so am recreating but am getting the following error which I didn't get before. Using proc contents, the two variables with 'var' in their name are both numeric, date9. variables.  Why am I getting this error?  Any help you can give is much appreciated!!

1427  *--correct varicella dose order;

1428

1429  array v{*} var:;

ERROR: All variables in array list must be the same type, i.e., all numeric or character.

1430

1431  call sortn(of v{*});

           -----

           731

WARNING 731-185: Argument #4 is a character variable, while a numeric variable must be passed to

                 the SORTN subroutine call in order for the variable to be updated.

1432

1433  n = nmiss(of v{*});

1434

1435  do i = 1 to dim(v)-n;

1436

1437      v{i} = v{i+n};

1438

1439      end;

1440

1441  do i = 1 to n;

1442

1443      call missing(v{dim(v)-i+1});

1444

1445      end;

1446

1447  drop i n;

1448  run;

[ballardw]

I would look for more variables that start with VAR than you expect, especially if you reimported the raw data.

post the full output of proc contents that you used.

[jcis7]

Thanks!  I separated it out from another block of code and it now works! 

[slchen]

Also try this. Question is: how do you know this is just due to missing value or no injection for third time with sort.

data have;
input school $ childnum (hepb1 hepb2 hepb3) (:mmddyy10.);
format hepb1-hepb3 yymmdd.;
datalines;
P001 001 01/25/2008 . 07/25/2008
P001 002 10/11/2008 07/10/2008 04/10/2007
P002 001 . 05/15/2008 02/11/2008
;
run;

data want(rename=(h1-h3=hepb1-hepb3));
set have;
array h(3);
do i=1 to dim(h);
  h(i)=smallest(i,of hepb1-hepb3);
end;
format h1-h3 yymmdd.;
drop hepb: i;
run;

[Ksharp]

Or don't use any function.

data have;
input school $ childnum (hepb1 hepb2 hepb3) (:mmddyy10.);
format hepb1-hepb3 yymmdd.;
datalines;
P001 001 01/25/2008 10/24/2007 07/25/2008
P001 002 10/11/2008 07/10/2008 04/10/2007
P002 001 11/15/2007 05/15/2008 02/11/2008
P002 003 05/15/2008 . 02/11/2008
P002 004 . . 02/11/2008
;
run;
data have;
 set have;
 array h{*} hepb:;
 do i=1 to dim(h)-1;
  do j=i+1 to dim(h);
   if (h{i} > h{j} and not missing(h{j})) or missing(h{i}) then do;
     temp=h{j};h{j}=h{i};h{i}=temp;
     end;
end;
end;
drop i j temp;
run;

Xia Keshan

[jcis7]

Thank you so very much everyone