02-14-2015 12:13 PM
I would like to create a macro which would produce the % of missing values for each variable in each year.
I'm using SAS 9.2. My datasets are in SAS view format and the sample looks less like that:
My desired excel output would look like this:
I would highly appreciate any help.
Have a nice weekend!
02-14-2015 01:29 PM
You don't need a macro. It would be a bit easier if you had SAS 9.3 because then you wouldn't need to transpose the data. I'm also not sure the IDLABEL statement is available for proc transpose in SAS 9.2 but you can remove that line without a major impact to your results.
Here's one way, without a macro:
infile cards truncover;
year V2 V3 V4;
1999 . 30
1999 . .
1999 231 23
2000 . 25
array v(3) v2-v4;
do i=1 to dim(v);
drop v2-v4 i;
proc means data=havet noprint;
class year vart;
output out=want1 n=N nmiss=Nmiss;
format pct_miss percent8.1;
proc sort data=want2;
by variable year;
proc transpose data=want2 out=want3(drop=_name_) Prefix=Year;
title 'Percent Missing, By Year';
proc print data=want3 noobs label;
02-16-2015 10:45 AM
Thanks a lot for your answer. I'm trying to run your program but when I'm running the "proc means" statements I'm getting the "ERROR: Variable value in list does not match type prescribed for the list".
I should have probably mentioned that most of my variables are character type. The tables that I attached were just to present the general outline of my datasets - I have many of them, all in SAS view format, that's why I thought about creating a macro.
I'm a beginner in SAS programming, I would appreciate any tips how to solve that.
02-16-2015 12:52 PM
Here's a macro that does it for a full dataset, not by groups.
02-14-2015 11:14 PM
data have; infile cards truncover; input year V2 V3 V4; cards; 1998 1998 20 1999 . 30 1999 . . 1999 231 23 2000 . 25 ; run; proc sql; select cat('nmiss(',strip(name),')/count(*) as ',strip(name)) into : list separated by ',' from dictionary.columns where libname='WORK' and memname='HAVE' and name ne 'year'; create table temp as select year,&list from have group by year; quit; proc transpose data=temp out=want; id year; run;