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;
