BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kumarathevan
Fluorite | Level 6

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. 

 

MissingData.png

 

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.  

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Amir
PROC Star

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.

 

View solution in original post

8 REPLIES 8
jvdl
Obsidian | Level 7

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

 

if missing(prodname) then prodname = CATX(' ', category, subcategory);
Kumarathevan
Fluorite | Level 6

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
Amethyst | Level 16

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;
Thanks,
Jag
jvdl
Obsidian | Level 7

Yes, that should work.

Amir
PROC Star

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
Fluorite | Level 6

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

s_lassen
Meteorite | Level 14

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
Fluorite | Level 6
Thank you very much. I tried the method suggested by Amir and it worked well for my query!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 10374 views
  • 5 likes
  • 5 in conversation