DATA Step, Macro, Functions and more

Macro to create excel report for mulit-year file

Reply
New Contributor
Posts: 2

Macro to create excel report for mulit-year file

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!

Super User
Posts: 17,929

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

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;

New Contributor
Posts: 2

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

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

Super User
Posts: 17,929

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

Super User
Posts: 9,691

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

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

Ask a Question
Discussion stats
  • 4 replies
  • 232 views
  • 1 like
  • 3 in conversation