topic Re: How To Replace Missing Values In SaS ? in SAS Programming

How To Replace Missing Values In SaS ?

Midi — Tue, 08 Oct 2019 17:08:34 GMT

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.

Re: How To Replace Missing Values In SaS ?

novinosrin — Tue, 08 Oct 2019 17:14:35 GMT

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;

Re: How To Replace Missing Values In SaS ?

SuryaKiran — Tue, 08 Oct 2019 17:24:14 GMT

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;

Re: How To Replace Missing Values In SaS ?

hashman — Tue, 08 Oct 2019 21:10:25 GMT

@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.

Re: How To Replace Missing Values In SaS ?

Ksharp — Wed, 09 Oct 2019 12:33:40 GMT

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;

Re: How To Replace Missing Values In SaS ?

hashman — Thu, 10 Oct 2019 00:47:55 GMT

@Ksharp:

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

Re: How To Replace Missing Values In SaS ?

Ksharp — Thu, 10 Oct 2019 11:16:02 GMT

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 .

Re: How To Replace Missing Values In SaS ?

hashman — Thu, 10 Oct 2019 17:37:06 GMT

@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.

Re: How To Replace Missing Values In SaS ?

Tom — Thu, 10 Oct 2019 18:06:19 GMT

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.

Re: How To Replace Missing Values In SaS ?

hashman — Thu, 10 Oct 2019 18:15:58 GMT

@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.

Re: How To Replace Missing Values In SaS ?

Tom — Thu, 10 Oct 2019 18:22:06 GMT

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-missing/td-p/187662

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

Re: How To Replace Missing Values In SaS ?

hashman — Thu, 10 Oct 2019 18:27:44 GMT

@Tom:

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

Re: How To Replace Missing Values In SaS ?

data_null__ — Thu, 10 Oct 2019 18:35:58 GMT

@hashman I was thinking that perhaps one could use METHOD=IN(dataset) to somehow communicate a different replacement value for missing values. I do now know how it all works but maybe something you might want to try.

@hashman wrote:

@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.

Re: How To Replace Missing Values In SaS ?

data_null__ — Thu, 10 Oct 2019 18:39:01 GMT

@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-missing/td-p/187662
data want ;
 set have ;
 call stdize('none','missing=',1,max_item,tot_item);
 call stdize('none','missing=',0,max_days,min_days);
run;

Re: How To Replace Missing Values In SaS ?

data_null__ — Thu, 10 Oct 2019 18:47:28 GMT

@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?

Re: How To Replace Missing Values In SaS ?

data_null__ — Thu, 10 Oct 2019 19:01:38 GMT

You can use the UPDATE statement to accomplish this without too much work.

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 print;
   run;
data master / view=master;
   set have(keep=user_id);
   retain max_item tot_item 1 max_days min_days 0;
   run;

data missing2value;
   update master have;
   by user_id;
   run;
proc print;
   run;

Re: How To Replace Missing Values In SaS ?

hashman — Thu, 10 Oct 2019 18:55:07 GMT

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

Re: How To Replace Missing Values In SaS ?

FreelanceReinh — Thu, 10 Oct 2019 20:58:22 GMT

@hashman wrote:

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.

Neither "well versed," I'm afraid, nor "the original inventor" (as shown by data_null__'s much older post). 🙂

In fact, I believe it was the first and only time that I used CALL STDIZE in that thread in July. I had just gone through the list of CALL routines (knowing that some of them can change the values of several variables in a single call) until I found one that did the job.

For @Midi's task the UPDATE statement (as suggested by data_null__) seems more natural to me than a CALL routine which was actually designed for a different purpose.