Art, The code is as follows for the PROC SQL part-Please note that is a larger part of a macro- Thanks for looking at this- Lawrence %macro groupsxz (data=, var=,var1=,var2=,var3=); proc sort data=&data(keep=&var &var1 &var2 &var3) out=values nodupkey ; by &var3; run; data _null_; set values end=last; call symputx('sitea'||left(_n_),&var); call symputx('siteb'||left(_n_),&var1); call symputx('sitec'||left(_n_),&var2); if last then call symputx('count',_n_,'g'); run; %put _local_; PROC PRINTTO LOG='p:\LOG.txt'; DATA _NULL_ ; select(&var); %do i=1 %to &count; proc sql noprint nowarn; create table RN as select distinct fixdate, SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*) as RN format percent10., trim(left(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 RNT from SHAREPOINT as A where intnx('month',today(),-12)<=fixdate<intnx('month',today(),0) and INPUT(SUBSTR(A.Observed_Role_,1,2),8.) = 1 and UNIT in ("&&sitea&i") GROUP BY FIXDATE, UNIT; create table MD as select distinct fixdate, SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*) as MD format percent10., trim(left(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 MDT from SHAREPOINT as A where intnx('month',today(),-12)<=fixdate<intnx('month',today(),0) and INPUT(SUBSTR(A.Observed_Role_,1,2),8.) = 2 and UNIT in ("&&sitea&i") GROUP BY FIXDATE, UNIT; create table RT as select distinct fixdate, SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*) as RT format percent10., 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 RTT from SHAREPOINT as A where intnx('month',today(),-12)<=fixdate<intnx('month',today(),0) and INPUT(SUBSTR(A.Observed_Role_,1,2),8.) = 3 and UNIT in ("&&sitea&i") GROUP BY FIXDATE, UNIT; create table OTHER as select distinct fixdate, SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*) as OTHER format percent10., 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 OTHERT from SHAREPOINT as A where intnx('month',today(),-12)<=fixdate<intnx('month',today(),0) and INPUT(SUBSTR(A.Observed_Role_,1,2),8.) NOT IN (1:3) and UNIT in ("&&sitea&i") GROUP BY FIXDATE, UNIT; create table totals as select distinct fixdate, SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*) as P format percent10., 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 PT from SHAREPOINT as A where intnx('month',today(),-12)<=fixdate<intnx('month',today(),0) and UNIT in ("&&sitea&i") GROUP BY fixdate, UNIT; create table totalx as select distinct A.*,A.fixdate as DATE, B.*,C.*,D.*,E.* FROM RN AS A LEFT JOIN MD AS B ON A.FIXDATE=B.FIXDATE LEFT JOIN RT AS C ON A.FIXDATE=c.FIXDATE LEFT JOIN OTHER AS D ON A.FIXDATE=D.FIXDATE LEFT JOIN TOTALS AS E ON A.FIXDATE=E.FIXDATE; select put(fixdate,monyy5.) as MONTH length=5, CASE WHEN count(RNT)>0 THEN RNT ELSE "NO OBS" END AS RNT LENGTH=12, CASE WHEN count(MDT)>0 THEN MDT ELSE "NO OBS" END AS MDG LENGTH=12, CASE WHEN count(RTT)>0 THEN RTT ELSE "NO OBS" END AS RTT LENGTH=12, CASE WHEN count(OTHERT)>0 THEN OTHERT ELSE "NO OBS" END AS OTHERT LENGTH=12, PT length=12 INTO :MONTHG, :RNG, :MDG, :RTG, :OTH, :TOTG FROM totalx WHERE fixdate=intnx('month',today(),-1); QUIT;
... View more
