How To Replace Missing Values In SaS ?

[Midi]

Hello Everyone , I have This Table :

User_ID	Max_Item	Tot_Item	Max_Days	Min_Days
1452	12	20	50	10
1460	,	,	20	5
1465	10	12	,	,
1470	,	,	,	,

And What I want To do is , whenever it founds a Missing in Max_Items it will replace it with 1 , whenever it founds a Missing in Tot_Items it will replace it with 1 and whenever it founds a Missing in Max_Days it Will replace it with 0 , whenever it founds a Missing in Min_Days it Will replace it with 0.

Any Help Would Be Much Appreciated , Thank U.

[novinosrin]

data have;
input User_ID	Max_Item	Tot_Item	Max_Days	Min_Days;
cards;
1452	12	20	50	10
1460	.	.	20	5
1465	10	12	.	.
1470	. 	.	.	.
;

data want;
set have;
Max_Item=coalesce(Max_Item,1);
Tot_Item=coalesce(Tot_Item,1);
Max_Days=coalesce(Max_Days,0);
Min_Days=coalesce(Min_Days,0);
run;

[SuryaKiran]

Hello,

You may can also use array concept, like:

data want;
set have;

array change_1{3} var1 var2 var3;
array change_0{2} var4 var5;

do over change_1;
if change_1=',' then change_1='1';
end;

do over change_0;
if change_0=',' then change_0='0';
end;

run;

[hashman]

@Midi:

Since all your variables in question are numeric, you need but a single array:

data have ;                                                                                                                                                                                                                                                     
  input user_id max_item tot_item max_days min_days ;                                                                                                                                                                                                           
  cards ;                                                                                                                                                                                                                                                       
1452  12  20  50  10                                                                                                                                                                                                                                            
1460   .  .   20   5                                                                                                                                                                                                                                            
1465  10  12   .   .                                                                                                                                                                                                                                            
1470   .   .   .   .                                                                                                                                                                                                                                            
;                                                                                                                                                                                                                                                               
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
data want ;                                                                                                                                                                                                                                                     
  set have ;                                                                                                                                                                                                                                                    
  array vv max_item--min_days ;                                                                                                                                                                                                                                 
  do over vv ;                                                                                                                                                                                                                                                  
    if nmiss (vv) then vv = _i_ < 3 ;                                                                                                                                                                                                                           
  end ;                                                                                                                                                                                                                                                         
run ;             

Kind regards

Paul D.

[Ksharp]

data have ;                                                                                                                                                                                                                                                     
  input user_id max_item tot_item max_days min_days ;                                                                                                                                                                                                           
  cards ;                                                                                                                                                                                                                                                       
1452  12  20  50  10                                                                                                                                                                                                                                            
1460   .  .   20   5                                                                                                                                                                                                                                            
1465  10  12   .   .                                                                                                                                                                                                                                            
1470   .   .   .   .                                                                                                                                                                                                                                            
;                                                                                                                                                                                                                                                               
run ;   
proc stdize data=have out=temp missing=1 reponly;
var  max_item tot_item;
run;
proc stdize data=temp out=want missing=0 reponly;
var  max_days min_days;
run;

[hashman]

@Ksharp: 

Would be nice if that could be done in one pass, wouldn't it? 😉

[Ksharp]

No. I don't think so. Make it done in one pass/data step ,would make you to generate more error .

My first option is make code more readable and robust (you need make sure your result is right, in one data step often suffer more error/ or risk and more important is not readable which make code hard maintenance ),and last option is in one data step .

[hashman]

@Ksharp:

Methinks you've misunderstood me. I meant that it would be nice if proc STDIZE allowed to set some specified variables to one value and some other specified variables - to a different value, all in a single pass through the input data.  

[Tom]

Can you figure out how to get CALL STDIZE() function to do solve this? 

I cannot get it to work, but it looks like it should be able to do it.  The documentation is very short on examples.

[hashman]

@Tom: 

As it was @FreelanceReinh who ideated using CALL STDIZE for this kind of purpose in the first place and hence must be quite well versed on the subject, maybe the original inventor will chime in with something  instructive upon seeing the reference I included in this sentence.

Kind regards

Paul D.  

[Tom]

I found example from @data_null__ in another topic on this forum.  

https://communities.sas.com/t5/SAS-Data-Management/Assign-value-to-list-of-variables-similar-to-call...

data want ;
 set have ;
 call stdize('none','missing=',1,max_item,tot_item);
 call stdize('none','missing=',0,max_days,min_days);
run;

[hashman]

@Tom:

Thanks for digging it up. When it comes to SAS, @data_null__ is a veritable horn of plenty. 

[data_null__]

---

@hashman wrote:

@Tom:

Thanks for digging it up. When it comes to SAS, @data_null__ is a veritable horn of plenty. 

---

@hashman May I put this on my resume? 

[hashman]

@data_null__ : By all means, J. But methinks a resume is the last thing you'd need. 

[data_null__]

@Tom as I recall CALL STDIZE will be rather slow.

---

@Tom wrote:

I found example from @data_null__ in another topic on this forum.  

https://communities.sas.com/t5/SAS-Data-Management/Assign-value-to-list-of-variables-similar-to-call...

data want ;
 set have ;
 call stdize('none','missing=',1,max_item,tot_item);
 call stdize('none','missing=',0,max_days,min_days);
run;

---