topic Missing table in SAS Procedures

Missing table

silasskovsbo — Tue, 07 May 2013 09:49:19 GMT

Dear all.

I work with panel data (data sets with 5 mio. observations pr year and hundreds of variables) at University of Aarhus, department of Economics, and am looking for a program which can do the following:

For some variables are the all observations missing for some years.It would be of great use to have a program which could make a table with one observation for each year telling for all the variables whether this variable is fully missing for the specific year. Missing is defined as SAS standards for both numeric and characters.

Example of data

Header 1	Header 2	Header 3	Header 4
Personidentifier	Year	Male	Income
1	1999	1	1000
2	1999	0	1500
1	2000	1	.
2	2000	0	.
1	2001	1	1400
2	2001	0	.

And wanted table:

Header 1	Header 2	Header 3
Year	Male_miss	Income_miss
1999	0	0
2000	0	1
2001	0	0

Hope it makes sense and that somebody it able to help.

Thanks a lot!

Best // Silas

Re: Missing table

Ksharp — Tue, 07 May 2013 10:28:46 GMT

OK.

data have;
input Personidentifier     Year     Male     Income ;
cards;
1     1999     1     1000
2     1999     0     1500
1     2000     1     .
2     2000     0     .
1     2001     1     1400
2     2001     0     .
;
run;
proc sql;
create table want as
 select  Year,nmiss(Male)=count(*) as male_miss,nmiss(Income)=count(*) as income_miss
  from have
   group by Year ;
quit;

Ksharp

Re: Missing table

Haikuo — Tue, 07 May 2013 14:01:00 GMT

In case performance is one of your concerns (It seems to me you have a big table), comparing the following data step approach with Ksharp's Proc SQL, choose the one that runs faster:

Also note, the following solution can also ease your hard coding to some extend.

data have;

input Personidentifier Year Male Income;

cards;

1 1999 1 1000

2 1999 0 1500

1 2000 1 .

2 2000 0 .

1 2001 1 1400

2 2001 0 .

;

proc sql NOPRINT;

select name into :old separated by ' ' from dictionary.columns

where upcase(name) not in ('PERSONIDENTIFIER', 'YEAR')

AND LIBNAME='WORK' AND MEMNAME='HAVE';

SELECT cats(name,'_','miss') INTO :NEW separated by ' ' from dictionary.columns

where upcase(name) not in ('PERSONIDENTIFIER', 'YEAR')

AND LIBNAME='WORK' AND MEMNAME='HAVE';

QUIT;

data want;

retain year &new;

array new &new ;

do i=1 to dim(new);

new(i)=1;

end;

DO UNTIL (LAST.YEAR);

SET HAVE ;

BY YEAR NOTSORTED; /* IN CASE 'YEAR' IS ONLY CLUSTERED, NOT SORTED*/

array old &old.;

do i=1 to dim(old);

new(i)=ifn(not missing(old(i)),0,new(i));

end;

keep year &new;

run;

Haikuo