Hi @Ronein
Here is a piece of code that does what you want. My personal opinion is that it is a better idea to be content with the possibilities given in standard procedures, because going beyond that takes a lot extra coding that is difficult to reuse, maintain and document, but it can be done.
data rawdata;
input ID Ddate Ind Y;
cards;
1 1806 1 100
1 1807 1 200
1 1809 1 300
2 1809 1 400
2 1807 1 500
2 1806 2 600
2 1806 2 700
3 1805 3 800
3 1805 3 900
4 1805 3 100
5 1802 3 110
6 1802 3 120
7 1802 3 130
;
run;
/* First first value of Ind + min and max Ddates and put them in macro variables */
proc sql noprint;
select min(Ind), min(Ddate), max(Ddate) into :MinInd, :MinDdate, :MaxDdate
from rawdata;
quit;
/* Then create a work dataset for one value of Ind with all Ddates */
data w;
Ind = &MinInd;
do Ddate = &MinDdate to &MaxDdate;
output;
end;
run;
/* Join on rawdata, so first value of Ind has all values of Ddate with missing Y
in Ddates not coming from rawdata */
proc sql;
create table want as
select
coalesce(rawdata.Ind, w.Ind) as Ind,
coalesce(rawdata.Ddate, w.Ddate) as Ddate,
rawdata.Y
from rawdata full outer join w
on
rawdata.Ind = w.Ind
and rawdata.Ddate = w.Ddate;
quit;
/* The we need a format to represent 0 as missing, because Proc Tabulate will
show 0.0 in cells where a Ddate is present even if Y is missing */
proc format;
value zeromiss
0 = .
other = [comma32.1]
;
run;
/* One change to your Proc tabulate - use new format instead of comma32.1 */
PROC TABULATE DATA=want format=comma32.;
var Y;
CLASS Ind / ORDER=UNFORMATTED MISSING;
CLASS Ddate / ORDER=UNFORMATTED MISSING;
TABLE Ind="" ALL={LABEL="Total"},
Ddate=''*Y=''*N=''*f=zeromiss.
Y=''*N='Total'
/box='Indicator';
RUN;
Output:
... View more
