Hello
In proc tabulate I am using "N" stastistics to calculate number of observations in each category.
However in this example I want to calculate number of observations only for non missing values of ID var .
What is the way to do it please?
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 4 130
;
run;
%let minDdate=1801;
%let MaxDdate=1810;
%put &minDdate;
%put &MaxDdate;
%let minInd=1;
%let MaxInd=4;
%put &minInd;
%put &MaxInd;
%let date_MinDdate=%sysfunc(inputn(&MinDdate.,yymmn4.));
%let date_MaxDdate=%sysfunc(inputn(&MaxDdate.,yymmn4.));
%put &date_MinDdate. ;
%put &date_MaxDdate.
data _null_;
No=intck('month',&date_MinDdate.,&date_MaxDdate.);
call symput("No",trim(left(No)));
run;
%put &No;
%macro mmacro1;
%do i=0 %to &No.;
%do j=&minInd. %to &MaxInd.;
Ddate=put(intnx('month',&date_MinDdate.,&i.),yymmn4.)*1;
ind =&j.;
output;
%end;
%end;
%mend mmacro1;
/*כל הצירופים האפשריים של הקטגוריות*/
data Sheled;
%mmacro1;
run;
proc sql;
create table want as
select ID ,
coalesce(a.Ddate, b.Ddate) as Ddate,
coalesce(a.Ind, b.Ind) as Ind,
coalesce(a.Y,0) as Y
from rawdata as a
full outer join Sheled as b
on a.Ind = b.Ind and a.Ddate = b.Ddate
order by calculated Ddate,calculated Ind
;
quit;
/*Correct OUTCOME*/
PROC TABULATE DATA=want ;
var Y;
CLASS Ind / ORDER=UNFORMATTED MISSING;
CLASS Ddate / ORDER=UNFORMATTED MISSING;
TABLE Ind="" ALL={LABEL="Total"},
Ddate=''*Y=''*SUM=''*f=zeromiss.
Y=''*SUM='Total'
/box='Indicator';
RUN;
/*wrong OUTCOME*/
PROC TABULATE DATA=want ;
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;
Your request:
I want to calculate number of observations only for non missing values of ID var
assuming that proc tabulate should ignore those observation you can do;
proc tabulate data=want(where=(ID ne .)) ....
but then other variables of those observation will be ignored too.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.