Compare columns and make one column

Reply
Occasional Contributor
Posts: 5

Compare columns and make one column

[ Edited ]

data.PNG

 

Hi all the experts, 

How do I find the earliest date within dateofadmin, dateofdrug and dateofmed for each ID using SAS code?

(ex. the earliest date for ID110 is dateofadmin14842  ((14842<18390 and ignore the "."))    )

and then i want to put the earliest in a new column Smiley Frustrated 

the date has been transfer into numeric format already. 

However, i couldnt find the ways to compare numbers within columns Smiley Frustrated

Please kindly give me a hint , would really be appreciated!Cat Mad

 

Super User
Posts: 1,232

Re: Compare columns and make one column

[ Edited ]

data want;

set have;

      firstdate = .;

      array datex {3} dateofadmin dateofdrug dateofmed;

      do i=1 to 3;

               if datex(i) ne . then do;

                  if firstdate = .  then firstdate = datex(i);

                  else if datex(i) < firstdate  then firstdate = datex(i);

     end; end;

run;

Respected Advisor
Posts: 4,974

Re: Compare columns and make one column

The MIN function should ignore missing values:

 

earliest_date = min(dateofadmin, dateofdrug, dateofmed);

Respected Advisor
Posts: 4,606

Re: Compare columns and make one column

The min function accepts any number of arguments.

 

data want;
set have;
firstdate = min(dateofadmin, dateofdrug, dateofmed);
run;
PG
Occasional Contributor
Posts: 5

Re: Compare columns and make one column

thank you so much!

Now i have one more question

what if i wanna add one more column "where" is states where does the first date comes from?

 

like, 

擷取.PNG

 

thank you Smiley Happy

Super User
Posts: 1,232

Re: Compare columns and make one column

[ Edited ]

data want;

set have;

      firstdate = .;

      array datex {3} dateofadmin dateofdrug dateofmed;

      do i=1 to 3;

               if datex(i) ne . then do;

                  if firstdate = .  then do;

                      firstdate = datex(i);

                      where = vname(datex(i));  

                  end;

                  else if datex(i) < firstdate  then firstdate = datex(i);

     end; end;

run;

Respected Advisor
Posts: 4,974

Re: Compare columns and make one column

It's possible to shorten the code by a line or two using arrays, but this variation might be easier to understand.

 

data want;

set have;

earliest_date = min(dateofadmin, dateofdrug, dateofmed);

length varname $ 32;

if earliest_date > . then select (whichn(earliest_date, dateofadmin, dateofdrug, dateofmed));

   when (1) varname='dateofadmin';

   when (2) varname='dateofdrug';

   when (3) varname='dateofmed';

end;

run;

Ask a Question
Discussion stats
  • 6 replies
  • 196 views
  • 4 likes
  • 4 in conversation