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.
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.
Why not just use a DATA step with the CATX function?
if missing(prodname) then prodname = CATX(' ', category, subcategory);
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.
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;
Yes, that should work.
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.
Thank you very much. This worked for me. really appreciate it!
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.