BookmarkSubscribeRSS Feed
AlicjaJ
Calcite | Level 5

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

V2V3V4
1998.
199820
1999.
1999.
199923123
2000.

My desired excel output would look like this:

Header 1199819992000
V250%

66,6%

100%
V3
V4

I would highly appreciate any help.

Have a nice weekend!

4 REPLIES 4
Reeza
Super User

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;

AlicjaJ
Calcite | Level 5

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

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 4 replies
  • 736 views
  • 1 like
  • 3 in conversation