I have a report thats run daily. I need to run each day against the other to find the variables in 5 different columns that have either changed from 0 to 1 or 1 to 0, then summarize each type of count and total count of changes. I have tried to create a unique id and then do an inner join with a nested statement, but somehow i am getting the wrong results. Is there an easier way to accomplish this? Maybe with proc report, proc freq, or a proc compare with some sort of summary?
Proc SQL;
CREATE TABLE work.FileA_TempID
AS (select MemberKey,ReportingPopulationName,MeasureKey,SubMeasureKey,SampleStatus,PrimaryMember,HRSampleMemberID,DENOMCNT, NUMERCNT, ADNUMCNT,MRNUMCNT,SPNUMCNT,CONTRCNT, CATS(MeasureKey,SubMeasureKey,MemberKey) AS TempID
FROM ref.TX_24);
RUN;
proc sql;
CREATE TABLE work.FileB_TempID
AS (select MemberKey,ReportingPopulationName,MeasureKey,SubMeasureKey,SampleStatus,PrimaryMember,HRSampleMemberID,DENOMCNT2, NUMERCNT2, ADNUMCNT2,MRNUMCNT2,SPNUMCNT2, CONTRCNT2, CATS(MeasureKey,SubMeasureKey,MemberKey) AS TempID
FROM ref.TX_25);
RUN;
/*JOIN with Nested Statement*/
proc sql;
CREATE TABLE work.Filtered_Table AS
select FileA_TempID.MemberKey,FileA_TempID.TempID,FILEB_TEMPID.ReportingPopulationName,FILEB_TEMPID.MeasureKey, FILEB_TEMPID.SubMeasureKey, FILEB_TEMPID.SampleStatus, FILEB_TEMPID.PrimaryMember, FILEB_TEMPID.HRSampleMemberID, FILEA_TEMPID.DENOMCNT,FILEB_TEMPID.DENOMCNT2, FILEA_TEMPID.NUMERCNT,FILEB_TEMPID.NUMERCNT2,FILEA_TEMPID.ADNUMCNT, FILEB_TEMPID.ADNUMCNT2, FILEA_TEMPID.MRNUMCNT, FILEB_TEMPID.MRNUMCNT2,FILEA_TEMPID.SPNUMCNT, FILEB_TEMPID.SPNUMCNT2,FILEA_TEMPID.CONTRCNT, FILEB_TEMPID.CONTRCNT2
from work.fileb_tempid as FB inner join work.filea_tempid as FA
on FB.TempID=FA.TempID
where
(FB.NUMERCNT2=0 and FA.NUMERCNT=1) or (FB.NUMERCNT2=1 and FA.NUMERCNT=0) or (ADNUMCNT2=0 and FA.ADNUMCNT=1)or (FB.ADNUMCNT2=1 and FA.ADNUMCNT=0)
or (FB.SPNUMCNT2=1 and FA.SPNUMCNT=0)or(FB.SPNUMCNT2=0 and FA.SPNUMCNT=1)
or (FB.CONTRCNT2=1 and FA.CONTRCNT=0)or (FB.CONTRCNT2=0 and FA.CONTRCNT=1)or(FB.MRNUMCNT2=1 and FA.MRNUMCNT=0)or (FB.MRNUMCNT2=0 and FA.MRNUMCNT=1);
Run;
proc sql;
create table work.Final as
select TempID,MemberKey,MeasureKey,SubMeasureKey,ADNUMCNT,ADNUMCNT2,MRNUMCNT,MRNUMCNT2,SPNUMCNT,SPNUMCNT2,CONTRCNT,CONTRCNT2,ReportingPopulationName,SampleStatus,PrimaryMember,HRSampleMemberID
from work.Filtered_Table;
Quit;