HOW TO REPLACE VALUE "0" TO Missing for the entire dataset USING CALL MISSING FUNCTION AND PROC SQL

Reply
Occasional Contributor
Posts: 6

HOW TO REPLACE VALUE "0" TO Missing for the entire dataset USING CALL MISSING FUNCTION AND PROC SQL

 
Super User
Super User
Posts: 7,720

Re: HOW TO REPLACE VALUE "0" TO Missing for the entire dataset USING CALL MISSING FUNCTION

You may want to post some example, showing what you have tried.  Why do you need to use call missing function, why do you need to use SQL etc?  You could trysomething like (not tested)

data want;
  set have;
  array var{*} _numeric_;
  do i=1 to dim(var{*});
    if var{i}=0 then var{i}=.;
  end;
run;
Occasional Contributor
Posts: 6

Re: HOW TO REPLACE VALUE "0" TO Missing for the entire dataset USING CALL MISSING FUNCTION

[ Edited ]

Thanks RW9

do i=1 to dim(var{*});
    if var{i}=0 then var{i}=.;

 Here is what I tried:

data new;
set work.import;
array one _numeric_;
do over one;
if one=0 then one='.';
end;
array two _character_;
do over two;
if two=0 then two='.';
end;
run;

 

I could execute it as required but its shooting some notes like this

 

NOTE: Invalid numeric data, '.123456' , at line 64 column 4.
NOTE: Invalid numeric data, 'Male' , at line 64 column 4.
NOTE: Invalid numeric data, 'Married' , at line 64 column 4.
NOTE: Invalid numeric data, 'Employees' , at line 64 column 4.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
 
 
As a beginner I am still exploring different ways of exploring the solution. Just curious to know some other methods of doing it.
 
Super User
Posts: 11,134

Re: HOW TO REPLACE VALUE "0" TO Missing for the entire dataset USING CALL MISSING FUNCTION


beginner1 wrote:

 

I could execute it as required but its shooting some notes like this

 

NOTE: Invalid numeric data, '.3713759' , at line 64 column 4.
NOTE: Invalid numeric data, 'Male' , at line 64 column 4.
NOTE: Invalid numeric data, 'Married' , at line 64 column 4.
NOTE: Invalid numeric data, 'Employees' , at line 64 column 4.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
Customer_ID=3713759 Gender=Male Birth_Date=12/11/1981 Marital_Status=Married Occupation_Category=Employees Total_Installments=30
Total_Transactions=26 Total_Amount=3133.05 Clothing_Count=. Fitness_Count=. Jewelry_Count=. Clothing_Amount=. Fitness_Amount=.
Jewelry_Amount=. _I_=5 _ERROR_=1 _N_=20
 
As a beginner I am still exploring different ways of exploring the solution. Just curious to know some other methods of doing it.
 

The warnings say that you have character data where SAS is expecting numeric. Since WE don't know which is line 64 can't be real specific

Possibly

data new;
   set work.import;
   array one _numeric_;
   do over one;
      if one=0 then one=.;
   end;
   array two _character_;
   do over two;
      if two='0' then two='';
   end;
run;
Occasional Contributor
Posts: 6

Re: HOW TO REPLACE VALUE "0" TO Missing for the entire dataset USING CALL MISSING FUNCTION

Thanks it worked.
Super Contributor
Posts: 415

Re: HOW TO REPLACE VALUE "0" TO Missing for the entire dataset USING CALL MISSING FUNCTION

Your errors are caused by this line:

 

if two=0 then two='.';

A character comparison requires character terms. And a missing charcater value is the empty string (or at least all spaces). So this would help:

 

if two='0' then two='';


Regards,

- Jan.

Occasional Contributor
Posts: 6

Re: HOW TO REPLACE VALUE "0" TO Missing for the entire dataset USING CALL MISSING FUNCTION

Thanks it worked.

Super User
Posts: 5,388

Re: HOW TO REPLACE VALUE "0" TO Missing for the entire dataset USING CALL MISSING FUNCTION

On top of my head, I don't think you can use call routines in PROC SQL. 

Why this specific requirement?

Data never sleeps
Ask a Question
Discussion stats
  • 7 replies
  • 447 views
  • 2 likes
  • 5 in conversation