BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jcis7
Pyrite | Level 9

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

12 REPLIES 12
PGStats
Opal | Level 21

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
jcis7
Pyrite | Level 9

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
Onyx | Level 15

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
Pyrite | Level 9

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

Haikuo
Onyx | Level 15

?

PGStats
Opal | Level 21

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
jcis7
Pyrite | Level 9

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;

ballardw
Super User

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
Pyrite | Level 9

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

slchen
Lapis Lazuli | Level 10

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
Super User

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
Pyrite | Level 9

Thank you so very much everyoneSmiley Happy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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