topic Re: write macro or automate process in SAS Programming

write macro or automate process

Satori — Thu, 09 Mar 2023 14:14:53 GMT

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?

Re: write macro or automate process

PaigeMiller — Thu, 09 Mar 2023 14:45:49 GMT

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?

Re: write macro or automate process

Satori — Thu, 09 Mar 2023 14:47:47 GMT

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.

Re: write macro or automate process

HarrySnart — Thu, 09 Mar 2023 14:52:18 GMT

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);

Re: write macro or automate process

Quentin — Thu, 09 Mar 2023 20:05:28 GMT

@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/858858/highlight/true#M339341

Did that macro not work for your need?

Re: write macro or automate process

Satori — Sat, 11 Mar 2023 16:09:42 GMT

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