topic Re: Filling empty cells with another column`s variable in SAS Programming

Filling empty cells with another column`s variable

JibJam221 — Sun, 07 Feb 2021 00:14:43 GMT

Hi everyone, hoping to get some help on this one.

I have a table that holds customerIDs. There are two other columns called `dateofpurchase` and 'dateofvisit'. When 'dateofpurchase' is empty, im hoping to full these cells with the same 'dateofvisit' values, kind of like a default. However, if the 'dateofpurchase' is not empty, I want to keep it as is.

I do not have code to attach since I don't know where to start.

Thanks!

Re: Filling empty cells with another column`s variable

Astounding — Sun, 07 Feb 2021 02:33:50 GMT

Surely you could give us some sample data .... what it looks like before and what you want it to look like. Be sure to include one example where a CustomerID is missing its very first dateofpurchase.

Re: Filling empty cells with another column`s variable

ballardw — Sun, 07 Feb 2021 04:49:19 GMT

One way would look something like this:

data want;
   set have;
   if missing(dateofpurchase) then dateofpurchase=dateofvisit;
run;

The missing function tests a variable to see if it is missing and returns "true" (actually a numeric value of 1) when the variable does not have a value. If the variable is character consisting only of blanks it will also result in true.

Re: Filling empty cells with another column`s variable

JibJam221 — Sun, 07 Feb 2021 23:35:14 GMT

thank you for this! Question - do you know if there is a way to do this for character variables as well?

Re: Filling empty cells with another column`s variable

Phil_NZ — Mon, 08 Feb 2021 18:36:17 GMT

I think you can do

data want (rename(dateofpurchase1=dateofpurchase) drop=dateofpurchase1 );
   set have;
   dateofpurchase1= ifn(' ',dateofvisit,dateofpurchase)
run;

And regarding the character value , you can change from "ifn" to "ifc" as @ballardw mentioned.

Re: Filling empty cells with another column`s variable

andreas_lds — Mon, 08 Feb 2021 07:50:18 GMT

@JibJam221 wrote:
thank you for this! Question - do you know if there is a way to do this for character variables as well?

The code posted by @ballardw works idependent of the variables' type. Both variables used in the assignment must have, of course, the same type.

Re: Filling empty cells with another column`s variable

ballardw — Mon, 08 Feb 2021 15:18:22 GMT

@Phil_NZ wrote:

I think you can do
data want (rename(dateofpurchase1=dateofpurchase) drop=dateofpurchase1 );
   set have;
   dateofpurchase1= ifn(' ',dateofvisit,dateofpurchase)
run; 
And I think it can be applied for both numeric and character types of var.

Actually IFN works with numeric values. Character values require IFC.

Re: Filling empty cells with another column`s variable

JibJam221 — Mon, 08 Feb 2021 16:21:39 GMT

it works! Thank you x a million!

Re: Filling empty cells with another column`s variable

JibJam221 — Mon, 08 Feb 2021 16:22:28 GMT

thank you!