write macro or automate process

[Satori]

I have this code:

data want1; set have; if missing(assets) then delete; if assets<0 then delete;
proc format; value missfmt .,0 ='Missing' other='Not Missing'; proc freq data=want1; format _NUMERIC_ missfmt.;
	tables _NUMERIC_ / missing missprint nocum;

After consulting the proc freq results I see that, after assets, fixed_assets is the next variable with more non missing observations so I run the following code:

data want2; set want1; if missing(fixed_assets) then delete; if fixed_assets<0 then delete;
proc format; value missfmt .,0 ='Missing' other='Not Missing'; proc freq data=want2; format _NUMERIC_ missfmt.;
	tables _NUMERIC_ / missing missprint nocum;

after I see from proc freq that the next variable is sales so I run the code again:

data want3; set want2; if missing(sales) then delete;
proc format; value missfmt .,0 ='Missing' other='Not Missing'; proc freq data=want3; format _NUMERIC_ missfmt.;
	tables _NUMERIC_ / missing missprint nocum;

I have list of 50 variables to go through, so I would like to automate this process and not have it done manually. How can I achieve this?

[PaigeMiller]

We have already discussed this with you in your earlier threads, in which @Reeza made the valuable suggestion to use the Missing Pattern report from PROC MI; and in which I also made suggestions regarding fitting models when there is lots of missing data. What is wrong with using those suggestions?

[Satori]

My only goal is to learn how to automate this process, not so much on the appropriate way of dealing with missing data. The suggestions on earlier threads focused on the best ways of dealing with missing data so I didn't find them helpful for my purpose.

[HarrySnart]

Hi @Satori 

If you need to do it in a custom way you can automate it with a macro. Simple (and imperfect) example below

data have;
input x y z;
datalines ;
1 2 3
. 1 3
1 . 4
4 3 .
3 4 .
;

%macro removeNulls(indat);
ods output variables=variables;
proc contents data=&indat;run;

data vars;
set variables;
idx+1;
keep idx variable;
run;

proc sql noprint; select max(idx) into :varmax from vars;quit;

%do i=1 %to &varmax.;

proc sql noprint; select variable into :curvar from vars where idx=&i.;quit;

data &indat;
set &indat;
if missing(&curvar.) then delete;
if &curvar. < 0 then delete;
run;

%end;

%mend;

%removeNulls(have);

[Quentin]

---

@Satori wrote:
My only goal is to learn how to automate this process, not so much on the appropriate way of dealing with missing data. The suggestions on earlier threads focused on the best ways of dealing with missing data so I didn't find them helpful for my purpose.

---

I posted a macro in that thread which tried to implement your logic:

https://communities.sas.com/t5/SAS-Programming/dynamic-counting-of-non-missing-observations/m-p/8588...

Did that macro not work for your need?

[Satori]

I was not able to make it work. Not very good at writing macros. Still learning.