topic Re: Fill Missing Values in table in SAS Procedures

Fill Missing Values in table

Kumarathevan — Mon, 25 Nov 2019 09:08:37 GMT

Hello All,

I am a total beginner to SAS and was hoping someone could help me with one a replace missing data issue.

I have a table as below where I have filtered to only display Product Name with Missing values which is blank. I am trying to find a method to replace those missing values in this table to be filled with a combination of the Category and Sub-Category.

For example Row 1 Product name will be Technology Copiers. Below is the code i used to filter this table.

PROC SQL;
CREATE TABLE WORK.QUERY2
AS
SELECT APACSALESDATA.Category, APACSALESDATA.'Sub-Category'n, APACSALESDATA.'Product Name'n
FROM WORK.APACSALESDATA APACSALESDATA
WHERE
(
(
( APACSALESDATA.Category = '' ) OR
( APACSALESDATA.'Sub-Category'n = '' )
) OR APACSALESDATA.'Product Name'n IS MISSING
) ;
QUIT;

I have tried the recode method, however I am not getting any results.

Appreciate any help on this subject.

Re: Fill Missing Values in table

jvdl — Mon, 25 Nov 2019 09:33:59 GMT

Why not just use a DATA step with the CATX function?

if missing(prodname) then prodname = CATX(' ', category, subcategory);

Re: Fill Missing Values in table

Kumarathevan — Mon, 25 Nov 2019 10:13:42 GMT

Hello, Thank you for replying promptly.

Would the query be something like this? Again apologize as I am still having a tough time figuring this out.

Data work.apacstore;
if missing(ProductName) then ProductName = CATX(' ', category, subcategory);
Run;

Kumar.

Re: Fill Missing Values in table

Jagadishkatam — Mon, 25 Nov 2019 10:26:58 GMT

you can do it proc sql as well like below

proc sql,
create table want as select category,subcategory, 
case when productname='' then catx(' ',category,subcategory), else productname end as productname from have;
quit;

Re: Fill Missing Values in table

Amir — Mon, 25 Nov 2019 10:41:08 GMT

If you want to use a data step then you could use:

/* set up data */
data have;
   set sashelp.class;

   /* set all names that begin with 'J' to missing */
   if name eq: 'J' then
      call missing(name);
run;

/* create a separate data set replace missing names, using a data step */
data want;
   set have;

   /* if name is missing then conjoin sex and age */
   if missing(name) then
      name = catx(' ',sex,age);
run;

If you want to use SQL then you could use:

/* set up data */
data have2;
   set sashelp.class;

   /* set all names that begin with 'J' to missing */
   if name eq: 'J' then
      call missing(name);
run;

/* replace missing names in original data set, using sql */
proc sql noprint;
   update
      have2
   set
      name = catx(' ',sex,age)
   where
      name is missing
   ;
quit;

Kind regards,

Amir.

Re: Fill Missing Values in table

s_lassen — Mon, 25 Nov 2019 13:50:12 GMT

You can use the coalesce function to fill in missing values, e.g.:

proc sql;
create table want as select
  Category,
  'Sub-Category'n,
  coalesce('Product Name'n,catx(' ',Category,'Sub-Category'n)) as 'Product Name'n
from have;

Or you can update your table in place, if that is more appropriate:

proc sql;
  update have
  set 'Product Name'n=catx(' ',Category,'Sub-Category'n))
  where 'Product Name'n is null;

Re: Fill Missing Values in table

jvdl — Mon, 25 Nov 2019 15:33:49 GMT

Yes, that should work.

Re: Fill Missing Values in table

Kumarathevan — Tue, 26 Nov 2019 03:14:47 GMT

Thank you very much. This worked for me. really appreciate it!

Re: Fill Missing Values in table

Kumarathevan — Tue, 26 Nov 2019 03:15:36 GMT

Thank you very much. I tried the method suggested by Amir and it worked well for my query!