Hi!
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:
year | V2 | V3 | V4 |
---|---|---|---|
1998 | . | ||
1998 | 20 | ||
1999 | . | ||
1999 | . | ||
1999 | 23123 | ||
2000 | . |
My desired excel output would look like this:
Header 1 | 1998 | 1999 | 2000 |
---|---|---|---|
V2 | 50% | 66,6% | 100% |
V3 | |||
V4 |
I would highly appreciate any help.
Have a nice weekend!
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:
data have;
infile cards truncover;
input
year V2 V3 V4;
cards;
1998
1998 20
1999 . 30
1999 . .
1999 231 23
2000 . 25
;
run;
data havet;
set have;
array v(3) v2-v4;
do i=1 to dim(v);
value=v(i);
vart=vname(v(i));
output;
end;
drop v2-v4 i;
run;
proc means data=havet noprint;
class year vart;
types year*vart;
var value;
output out=want1 n=N nmiss=Nmiss;
run;
data want2;
set want2;
pct_miss=nmiss/(n+nmiss);
format pct_miss percent8.1;
run;
proc sort data=want2;
by variable year;
run;
proc transpose data=want2 out=want3(drop=_name_) Prefix=Year;
by variable;
id year;
idlabel year;
var pct_miss;
run;
title 'Percent Missing, By Year';
proc print data=want3 noobs label;
run;
title;
Hi Reeza,
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.
Best regard,
Alicja
Here's a macro that does it for a full dataset, not by groups.
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;
Xia Keshan
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.