Dataset Structure - Pairs of Observations

[JJ_83]

Hello, 

I have the following dataset:

data have;
	input cancer_year cancer_site $10. value_1 value_2 value_3 value_4;
datalines;
1990 breast     100 100 100 100
1990 colorectal 2 1 5 10
1990 lung       8 78 64 30
1990 overall    1 2 3 4
1990 prostate   100 200 300 400
1990 thyroid    0 0 3 4
1991 breast     9 8 7 6
1991 overall    50 60 70 80
1992 overall    10 20 30 40
;

But I want the following dataset. 

data want; 
	input cancer_year cancer_site $10. value_1 value_2 value_3 value_4;
datalines;
1990 breast 	100 100 100 100
1990 colorectal 2 1 5 10
1990 lung 		8 78 64 30
1990 overall 	1 2 3 4
1990 prostate 	100 200 300 400
1990 thyroid 	0 0 3 4
1991 breast 	9 8 7 6
1991 colorectal 0 0 0 0
1991 lung 		0 0 0 0
1991 overall 	50 60 70 80
1991 prostate 	0 0 0 0
1991 thyroid 	0 0 0 0
1992 breast 	0 0 0 0
1992 colorectal 0 0 0 0
1992 lung 		0 0 0 0
1992 overall 	10 20 30 40
1992 prostate 	0 0 0 0
1992 thyroid 	0 0 0 0
;
run;

What I want is the 6 cancer_site values per each year. And I want zeros for value_1-value_4 for pairs not in the original dataset.

Any suggestions?

[Reeza · Accepted solution]

Here's one way by building a skeleton table. 

PRELOADFMT works well in PROC TABULATE but will only generate output not a data set. 

CLASSDATA also works but doesn't set the values to 0 automatically. 

data have;
	input cancer_year cancer_site $10. value_1 value_2 value_3 value_4;
datalines;
1990 breast     100 100 100 100
1990 colorectal 2 1 5 10
1990 lung       8 78 64 30
1990 overall    1 2 3 4
1990 prostate   100 200 300 400
1990 thyroid    0 0 3 4
1991 breast     9 8 7 6
1991 overall    50 60 70 80
1992 overall    10 20 30 40
;

proc sql;
create table cancer_sites as
select distinct t1.cancer_site, t2.cancer_year, 0 as value_1, 0 as value_2, 0 as value_3, 0 as value_4
from have as t1, have as t2
order by 2, 1;
quit;

proc sort data=have;
by cancer_year cancer_site;
run;


data want;
update cancer_sites have;
by cancer_year cancer_site;
run;