topic Re: Replace Missing Data by Calculating the difference between 2 other columns in SAS Studio

Replace Missing Data by Calculating the difference between 2 other columns

Kumarathevan — Tue, 26 Nov 2019 11:04:50 GMT

Hello All,

I am trying to write a query to replace missing values for the column Shipmode and they have a correlation with the difference of ShipDate and OrderDate. There are 4 types of ShipMode,

Same day if the ship date and order date are the same, First class if its below 3 days, Second class if its below 4 days and everything else is Standard Class

I tried the following query with no luck. It is replacing my original dataset with only the values of those with the missing values. I am an absolute beginner to SAS, so any help on this would be deeply appreciated

data apacstore;

set work.apacstore;

if shipdate-orderdate <=1 then shipmode = 'Same Day';
if shipdate-orderdate > 1 or shipdate-orderdate < 3 then shipmode = 'First Class';
if shipdate-orderdate >= 3 or shipdate-orderdate <= 4 then shipmode 'Second Class';
if shipdate-orderdate > 4 then shipmode = 'Standard Class';
where shipmode is missing;
run;

Re: Replace Missing Data by Calculating the difference between 2 other columns

Amir — Tue, 26 Nov 2019 11:22:05 GMT

The where statement causes the data step to only read in the records with a missing shipmode, so that is why you do not see any records with a non-missing value for shipmode.

An alternative is to use conditional logic, as below, with an if statement, so that all records go to the output data set. I have also updated some of the logic by using else as well.

data apacstore;

set work.apacstore;

   if missing(shipmode) then
   do;
      if shipdate-orderdate <=1 then
         shipmode = 'Same Day';
      else
         if shipdate-orderdate < 3 then
            shipmode = 'First Class';
         else
            if shipdate-orderdate <= 4 then
               shipmode = 'Second Class';
            else
               shipmode = 'Standard Class';
   end;
run;

Kind regards,

Amir.

Re: Replace Missing Data by Calculating the difference between 2 other columns

Kumarathevan — Wed, 27 Nov 2019 01:20:46 GMT

thank you very much. I have a better understanding on how the where statement works now!

Re: Replace Missing Data by Calculating the difference between 2 other columns

Tom — Wed, 27 Nov 2019 01:42:47 GMT

Note that placing the WHERE statement miles away from the SET statement is probably contributing to this type of confusion. It doesn't confuse SAS, but will most certainly confuse programmers looking at the code into the false impression that the WHERE is the last thing that happens. If you want to use WHERE on the way out of the data step use the WHERE= dataset option on the output dataset specification.

Also clear up your logic in your IF/THEN sequence.. Your existing code will only create the first two levels of SHIPMODE, since any number (including missing values) is either greater than 1 or it isn't.

data apacstore(where=(shipmode is missing;));
  set work.apacstore;
  if (shipdate-orderdate) >.Z AND (shipdate-orderdate) <=1 then shipmode = 'Same Day';
  else if (shipdate-orderdate) > 1 AND (shipdate-orderdate) < 3 then shipmode = 'First Class';
  else if (shipdate-orderdate) >= 3 AND (shipdate-orderdate) <= 4 then shipmode 'Second Class';
  else if shipdate-orderdate > 4 then shipmode = 'Standard Class';
run;

Re: Replace Missing Data by Calculating the difference between 2 other columns

Kumarathevan — Wed, 27 Nov 2019 01:58:02 GMT

Thank You Tom! really appreciate your insights on these best practices. I am a total noob trying to complete an assignment here. Learning tonnes through this forum!