proc tabulate -number of obs with non missing value in ID value

Ronein — Tue, 05 Mar 2019 11:57:21 GMT

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;

Re: proc tabulate -number of obs with non missing value in ID value

Shmuel — Tue, 05 Mar 2019 14:07:08 GMT

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.

topic Re: proc tabulate -number of obs with non missing value in ID value in SAS Programming

proc tabulate -number of obs with non missing value in ID value

Re: proc tabulate -number of obs with non missing value in ID value