Fill Missing Values in table

[Kumarathevan]

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.  

[jvdl]

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

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

[Kumarathevan]

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.

[Jagadishkatam]

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;

[jvdl]

Yes, that should work.

[Amir · Accepted solution]

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.

[Kumarathevan]

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

[s_lassen]

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;

[Kumarathevan]

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