BookmarkSubscribeRSS Feed
Satori
Quartz | Level 8

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?

 

 

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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?

--
Paige Miller
Satori
Quartz | Level 8
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
SAS Employee

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
Super User

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

 

 

BASUG is hosting free webinars Next up: Mark Keintz presenting History Carried Forward, Future Carried Back: Mixing Time Series of Differing Frequencies on May 8. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Satori
Quartz | Level 8
I was not able to make it work. Not very good at writing macros. Still learning.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 436 views
  • 0 likes
  • 4 in conversation