DATA Step, Macro, Functions and more

Correcting Dose Order

Accepted Solution Solved
Reply
Regular Contributor
Posts: 192
Accepted Solution

Correcting Dose Order

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!


Accepted Solutions
Solution
‎06-03-2014 10:33 PM
Respected Advisor
Posts: 4,649

Re: Correcting Dose Order

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

PG

View solution in original post


All Replies
Respected Advisor
Posts: 4,649

Re: Correcting Dose Order

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

PG
Regular Contributor
Posts: 192

Re: Correcting Dose Order

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.

Respected Advisor
Posts: 3,124

Re: Correcting Dose Order

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

Regular Contributor
Posts: 192

Re: Correcting Dose Order

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

Respected Advisor
Posts: 3,124

Re: Correcting Dose Order

?

Solution
‎06-03-2014 10:33 PM
Respected Advisor
Posts: 4,649

Re: Correcting Dose Order

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

PG
Regular Contributor
Posts: 192

Re: Correcting Dose Order

I lost my code Smiley Sad 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;

Super User
Posts: 10,500

Re: Correcting Dose Order

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.

Regular Contributor
Posts: 192

Re: Correcting Dose Order

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

Super Contributor
Posts: 275

Re: Correcting Dose Order

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;

Super User
Posts: 9,681

Re: Correcting Dose Order

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

Regular Contributor
Posts: 192

Re: Correcting Dose Order

Thank you so very much everyoneSmiley Happy

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 404 views
  • 6 likes
  • 6 in conversation