topic Re: Macro to create excel report for mulit-year file in SAS Programming

Macro to create excel report for mulit-year file

AlicjaJ — Sat, 14 Feb 2015 17:13:58 GMT

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

Header 1

1998

1999

2000

50%

66,6%

100%

I would highly appreciate any help.

Have a nice weekend!

Re: Macro to create excel report for mulit-year file

Reeza — Sat, 14 Feb 2015 18:29:39 GMT

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;

Re: Macro to create excel report for mulit-year file

Ksharp — Sun, 15 Feb 2015 04:14:08 GMT

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

Re: Macro to create excel report for mulit-year file

AlicjaJ — Mon, 16 Feb 2015 15:45:03 GMT

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

Re: Macro to create excel report for mulit-year file

Reeza — Mon, 16 Feb 2015 17:52:02 GMT

Here's a macro that does it for a full dataset, not by groups.

SAS - Missing Macro - For a dataset, variable level report the number of missing and non-missing observations