Art; As it applies to this specific question- What I have thus far is below- Thanks for taking a look at it. Lawrence libname CHILLE "\xx\CHILLE.MDB"; filename hhmacros "\\xx\MACROS\HH"; filename mymacros "\\xx\MACROS"; options mrecall mautosource sasautos=(mymacros hhmacros sasautos); RUN; /*FOR UNITS*/ proc sql noprint; create table alludata as select distinct left(UNIT) as UNIT, CASE WHEN INPUT(SUBSTR(A.Observed_Role_,1,2),8.) in (1:20) THEN C.SASNAME ELSE 'OTHERS' END as ROLE, put(fixdate,monyy5.) as time, compress(put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*),percent10.)||'('||put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END),8.)||'/'||put(count(*),8.)||')') as compliance from SHAREPOINT as A LEFT JOIN CHILLE.HHROLES AS C ON SUBSTR(A.Observed_Role_,1,2)=SUBSTR(C.Sharepoint,1,2) where &curyr1<=fixdate<intnx('month',today(),0) and location NOT IN ('LPPI') GROUP BY UNIT, CALCULATED ROLE,fixdate OUTER UNION CORRESPONDING select distinct left(UNIT) as UNIT, CASE WHEN INPUT(SUBSTR(A.Observed_Role_,1,2),8.) in (1:20) THEN C.SASNAME ELSE 'OTHERS' END as ROLE, 'TOTAL' AS TIME, compress(put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*),percent10.)||'('||put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END),8.)||'/'||put(count(*),8.)||')') as compliance from SHAREPOINT as A LEFT JOIN CHILLE.HHROLES AS C ON SUBSTR(A.Observed_Role_,1,2)=SUBSTR(C.Sharepoint,1,2) where &curyr1<=fixdate<intnx('month',today(),0) and location NOT IN ('LPPI') GROUP BY UNIT, CALCULATED ROLE OUTER UNION CORRESPONDING select distinct left(UNIT) as UNIT, 'TOTAL' as ROLE, put(fixdate,monyy5.) as time, compress(put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*),percent10.)||'('||put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END),8.)||'/'||put(count(*),8.)||')') as compliance from SHAREPOINT as A LEFT JOIN CHILLE.HHROLES AS C ON SUBSTR(A.Observed_Role_,1,2)=SUBSTR(C.Sharepoint,1,2) where &curyr1<=fixdate<intnx('month',today(),0) and location NOT IN ('LPPI') GROUP BY UNIT, fixdate OUTER UNION CORRESPONDING select distinct left(UNIT) as UNIT, 'TOTAL' as ROLE, 'TOTAL' as time, compress(put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*),percent10.)||'('||put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END),8.)||'/'||put(count(*),8.)||')') as compliance from SHAREPOINT as A LEFT JOIN CHILLE.HHROLES AS C ON SUBSTR(A.Observed_Role_,1,2)=SUBSTR(C.Sharepoint,1,2) where &curyr1<=fixdate<intnx('month',today(),0) and location NOT IN ('LPPI') GROUP BY UNIT ORDER BY UNIT, CALCULATED ROLE, CALCULATED TIME; QUIT; proc sql; create table trial as select distinct role,compliance,time,count(*) as count from alludata where unit='06 Long' group by role order by role,time; QUIT; data wide_real; set trial; array AtopicA(4) $ 12 &mos; retain &mos; by role time; if first.role then do; i=1; do j = 1 to 4; AtopicA = ' '; end; end; AtopicA(i) = compliance; if last.role then output;/* outputs only the last obs per person */ I+1; run;
... View more
