Date: Fri, 10 Dec 2004 13:36:36 -0600
Reply-To: "Dunn, Toby" <Toby.Dunn@TEA.STATE.TX.US>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Dunn, Toby" <Toby.Dunn@TEA.STATE.TX.US>
Subject: Re: How to define a global determistic variable without using
Macro
Content-Type: text/plain; charset="us-ascii"
Okay Ian,
I think I am starting to see your point now. Perhaps I hate to give up
total the idea of global macro vars simply because it is a perfectly
good tool. The example was very contrived so to better explain look at
what I am given to work with and not given time to fix it properly (at
end of message warning if you have a weak heart don't read it). I
haven't written a program from scratch that is over 200 to 300 lines in
about 3 years. However I have been given code that is routinely over
1000 lines. So I giess I will have to acquiesce the point and say
perhaps the only place you would really want a global var is when you
want that variable's value to change between macros. Which has its own
set of problems as you have pointed out many times. Or when you are
trying to write code for a shop and trying to make your code fit there
style.
Now for some very offending code:
%LET PROGRAM = XXXXXXXX;
%LET PROJECT = 2004 ZZZZ;
%LET TASK = Construct ZZZZ Datasets;
%LET AUTHOR = xxx;
%LET DIVISION = SSSSSSSSSS;
%LET CY = 04;
%LET CFY = 2004;
%LET PY = 03;
%LET PFY = 2003;
<about seven libname statements here>
%MACRO CLASS(E);
&E.PCTGKGA &E.PCTGKGC &E.PCTGKGS
&E.PCTG01A &E.PCTG01C &E.PCTG01S &E.PCTG02A &E.PCTG02C &E.PCTG02S
&E.PCTG03A &E.PCTG03C &E.PCTG03S &E.PCTG04A &E.PCTG04C &E.PCTG04S
&E.PCTG05A &E.PCTG05C &E.PCTG05S &E.PCTG06A &E.PCTG06C &E.PCTG06S
&E.PCTENGA &E.PCTENGC &E.PCTENGS &E.PCTFLAA &E.PCTFLAC &E.PCTFLAS
&E.PCTMATA &E.PCTMATC &E.PCTMATS &E.PCTSCIA &E.PCTSCIC &E.PCTSCIS
&E.PCTSOCA &E.PCTSOCC &E.PCTSOCS &E.PCTGMEA &E.PCTGMEC &E.PCTGMES
%MEND CLASS;
%MACRO ETH(IVAR);
IF ETHNIC = '1' THEN &IVAR.INFC = FTE;
IF ETHNIC = '2' THEN &IVAR.PAFC = FTE;
IF ETHNIC = '3' THEN &IVAR.BLFC = FTE;
IF ETHNIC = '4' THEN &IVAR.HIFC = FTE;
IF ETHNIC = '5' THEN &IVAR.WHFC = FTE;
IF ETHNIC IN ('1','2') THEN &IVAR.OEFC = FTE;
%MEND ETH;
*=================================*
* Campus Processing *
*-----------------------------------------------------------------*;
PROC SQL;
CONNECT TO DB2;
CREATE TABLE WORK.CNAME AS /*CAMPUS NAMES*/
SELECT CAMPUS, CAMPNAME
FROM CONNECTION TO DB2
(SELECT CAMPUS, CAMPNAME
FROM P.CAMPUS&CY.F)
ORDER BY CAMPUS;
CREATE TABLE WORK.PROGRAM AS /*TEACHERS BY PROGRAM*/
SELECT CAMPUS, POPSERV, DISTRICT, FTE
FROM CONNECTION TO DB2
(SELECT CAMPUS, POPSERV, DISTRICT, FTE
FROM P.CAMPUS_POPSERV&CY.F
WHERE ROLEGRP2 = '21')
ORDER BY CAMPUS;
DISCONNECT FROM DB2;
QUIT;
DATA PROGRAM (DROP = FTE POPSERV);
LENGTH DISTRICT $6.;
SET PROGRAM;
IF POPSERV = '01' THEN PSTREFC = FTE;
IF POPSERV = '05' THEN PSTVOFC = FTE;
IF POPSERV IN ('02','07') THEN PSTBIFC = FTE;
IF POPSERV = '03' THEN PSTCOFC = FTE;
IF POPSERV = '04' THEN PSTGIFC = FTE;
IF POPSERV = '06' THEN PSTSPFC = FTE;
IF POPSERV IN ('00','08','09','10') THEN PSTOPFC = FTE;
PSTGOFC = SUM(PSTGIFC,PSTOPFC);
IF DISTRICT NE SUBSTR(CAMPUS,1,6) THEN DO;
PUT _ALL_;
DISTRICT = SUBSTR(CAMPUS,1,6);
END;
PROC SUMMARY DATA = PROGRAM NWAY;
VAR PSTREFC PSTVOFC PSTBIFC PSTCOFC
PSTGIFC PSTSPFC PSTOPFC PSTGOFC;
CLASS CAMPUS;
ID DISTRICT;
OUTPUT OUT = CPROG (DROP = _TYPE_ _FREQ_)
SUM = CPSTREFC CPSTVOFC CPSTBIFC CPSTCOFC
CPSTGIFC CPSTSPFC CPSTOPFC CPSTGOFC;
PROC SUMMARY DATA = PROGRAM NWAY;
VAR PSTREFC PSTVOFC PSTBIFC PSTCOFC
PSTGIFC PSTSPFC PSTOPFC PSTGOFC;
CLASS DISTRICT;
OUTPUT OUT = DPROG (DROP = _TYPE_ _FREQ_)
SUM = DPSTREFC DPSTVOFC DPSTBIFC DPSTCOFC
DPSTGIFC DPSTSPFC DPSTOPFC DPSTGOFC;
PROC SQL;
CONNECT TO DB2;
CREATE TABLE WORK.CETHNIC AS
SELECT CAMPUS, ETHNIC, FTE, ROLEGRP2
FROM CONNECTION TO DB2
(SELECT CAMPUS, ETHNIC, FTE, ROLEGRP2
FROM P.CAMPUS_DEMOG&CY.F
WHERE CAMPUS NOT LIKE '%7XX' AND
ROLEGRP2 NOT IN ('11','12','13','14','19','24'))
ORDER BY CAMPUS;
DISCONNECT FROM DB2;
QUIT;
DATA CETHNIC (DROP = FTE ETHNIC);
SET CETHNIC;
%ETH(CPSA);
PROC SUMMARY DATA = CETHNIC NWAY;
VAR _NUMERIC_;
CLASS CAMPUS;
OUTPUT OUT = CETHNIC (DROP = _TYPE_ _FREQ_) SUM =;
PROC SQL;
CONNECT TO DB2;
CREATE TABLE WORK.CONTRACT AS
SELECT *
FROM CONNECTION TO DB2
(SELECT CAMPUS, SUM(CONTRINSTR_FTE) AS CPSOTOFC
FROM P.CONTRACT_STAFF&CY.F
GROUP BY CAMPUS)
ORDER BY CAMPUS;
CREATE TABLE WORK.CTCHETH AS
SELECT CAMPUS, SEX, ETHNIC, SUM(FTE) AS FTE
FROM CONNECTION TO DB2
(SELECT CAMPUS, SEX, ETHNIC, FTE
FROM P.CAMPUS_DEMOG&CY.F
WHERE ROLEGRP2 = '21')
GROUP BY CAMPUS, SEX, ETHNIC
ORDER BY CAMPUS;
DISCONNECT FROM DB2;
QUIT;
DATA CTCHETH (DROP = SEX ETHNIC FTE);
SET CTCHETH;
%ETH(CPST);
IF SEX = 'M' THEN CPSTMAFC = FTE;
IF SEX = 'F' THEN CPSTFEFC = FTE;
PROC SUMMARY DATA = CTCHETH NWAY;
VAR _NUMERIC_;
CLASS CAMPUS;
OUTPUT OUT = CTCHETH (DROP = _TYPE_ _FREQ_) SUM=;
PROC SQL;
CONNECT TO DB2;
CREATE TABLE WORK.CAMPAGG AS
SELECT *
FROM CONNECTION TO DB2
(SELECT A.PERSONID, A.EXPER, A.TOTALPAY, A.BASEPAY, B.CAMPUS,
B.PFTE, B.PTIME, B.ROLEGRP2, B.ROLE
FROM P.EMPLOY&CY.F AS A, P.CAMPUS_ROLE&CY.F AS B
WHERE A.PERSONID = B.PERSONID
AND SUBSTR(B.CAMPUS,1,6) = A.DISTRICT)
ORDER BY CAMPUS;
DISCONNECT FROM DB2;
QUIT;
DATA CAMPAGG (DROP = ROLEGRP2 PFTE PTIME BASEPAY TOTALPAY EXPER
U1-U5 L1-L5);
SET CAMPAGG;
IF SUBSTR(CAMPUS,7,3)>='700' & SUBSTR(CAMPUS,4,3) NE '950' THEN DELETE;
IF EXPER = . THEN EXPER = 0;
ARRAY GRPS $ G1-G5 ('21','22','23','24','25');
ARRAY FTES CPSTTOFC CPSUTOFC CPSSTOFC CPSCTOFC CPSETOFC;
ARRAY BPAY CPSTTOST CPSUTOST CPSSTOST CPSCTOST CPSETOST;
ARRAY TPAY CPSTTOTT CPSUTOTT CPSSTOTT CPSCTOTT CPSETOTT;
DO OVER GRPS;
IF ROLEGRP2 = GRPS THEN DO;
FTES = PFTE;
BPAY = PTIME*BASEPAY;
TPAY = PTIME*TOTALPAY;
END;
END;
ARRAY ULS U1-U5 ( 1, 6, 11, 21, 99);
ARRAY LLS L1-L5 (-1, 0, 5, 10, 20);
ARRAY EFTES CPST00FC CPST01FC CPST06FC CPST11FC CPST20FC;
ARRAY EBPAY CPST00ST CPST01ST CPST06ST CPST11ST CPST20ST;
ARRAY ETPAY CPST00TT CPST01TT CPST06TT CPST11TT CPST20TT;
DO OVER EFTES;
IF ROLEGRP2 = '21' & (LLS < EXPER < ULS) THEN DO;
EFTES = PFTE;
EBPAY = PTIME*BASEPAY;
ETPAY = PTIME*TOTALPAY;
END;
END;
ARRAY C1 CPSTTOFC CPSTTOST CPSTTOTT CPSPTOFC CPSPTOST CPSPTOTT;
ARRAY C2 CPSUTOFC CPSUTOST CPSUTOTT CPSETOFC CPSETOST CPSETOTT;
ARRAY C3 CPSSTOFC CPSSTOST CPSSTOTT CPSCTOFC CPSCTOST CPSCTOTT;
ARRAY C4 CPSPTOFC CPSPTOST CPSPTOTT CPSATOFC CPSATOST CPSATOTT;
DO OVER C1;
C4 = SUM(C1,C2,C3);
END;
IF CPSCTOFC > 0 THEN CPSPTOFC = CPSPTOFC - CPSCTOFC;
CPST05FC = SUM(CPST00FC,CPST01FC);
CPST05ST = SUM(CPST00ST,CPST01ST);
CPST05TT = SUM(CPST00TT,CPST01TT);
PROC SUMMARY DATA = CAMPAGG NWAY;
VAR _NUMERIC_;
CLASS CAMPUS;
OUTPUT OUT = CAMPAGG (DROP = _TYPE_ _FREQ_) SUM =;
DATA CSTAFF;
SET CAMPAGG;
ARRAY FTS CPSUTOFC CPSSTOFC CPSCTOFC CPSTTOFC CPST00FC CPST01FC CPST05FC
CPST06FC CPST11FC CPST20FC CPSPTOFC CPSATOFC CPSETOFC;
ARRAY TTS CPSUTOTT CPSSTOTT CPSCTOTT CPSTTOTT CPST00TT CPST01TT CPST05TT
CPST06TT CPST11TT CPST20TT CPSPTOTT CPSATOTT CPSETOTT;
ARRAY TAS CPSUTOTA CPSSTOTA CPSCTOTA CPSTTOTA CPST00TA CPST01TA CPST05TA
CPST06TA CPST11TA CPST20TA CPSPTOTA CPSATOTA CPSETOTA;
ARRAY TBS CPSUTOST CPSSTOST CPSCTOST CPSTTOST CPST00ST CPST01ST CPST05ST
CPST06ST CPST11ST CPST20ST CPSPTOST CPSATOST CPSETOST;
ARRAY BAS CPSUTOSA CPSSTOSA CPSCTOSA CPSTTOSA CPST00SA CPST01SA CPST05SA
CPST06SA CPST11SA CPST20SA CPSPTOSA CPSATOSA CPSETOSA;
DO OVER FTS;
IF FTS NE . & FTS NE 0 THEN DO;
BAS = ROUND(TBS/FTS,1);
TAS = ROUND(TTS/FTS,1);
END;
END;
*=================================*
* Combine Campus Datasets *
*-----------------------------------------------------------------*;
DATA CSTAFF (DROP = FLAG SFLAG TFLAG)
MIS_FTE (DROP = FLAG SFLAG TFLAG)
MIS_SFTE (DROP = FLAG SFLAG TFLAG)
MIS_TFTE (DROP = FLAG SFLAG TFLAG)
NINE50S (DROP = FLAG SFLAG TFLAG);
MERGE CSTAFF (IN=A)
CETHNIC
CTCHETH
CNAME
COMMON.CAMPUS (KEEP = CAMPUS CPETALLC CAMPNAME
RENAME = (CPETALLC = C_ENROLL) IN=C)
TENURE.CAMPUS (RENAME = (FTE = CAVGNFTE) IN=B
DROP = WGTTEN WGTEXP)
CPROG
CONTRACT
CLASS.CAMPUS (KEEP = CAMPUS %CLASS(C));
BY CAMPUS;
IF (A | B);
ARRAY ZS CPSTTOFC CPSUTOFC CPSSTOFC CPSCTOFC CPSETOFC CPSTTOST
CPSUTOST CPSSTOST CPSCTOST CPSETOST CPSTTOTT CPSUTOTT CPSSTOTT
CPSCTOTT CPSETOTT CPST00FC CPST01FC CPST06FC CPST11FC CPST20FC
CPST00ST CPST01ST CPST06ST CPST11ST CPST20ST CPST00TT CPST01TT
CPST06TT CPST11TT CPST20TT CPSPTOFC CPSPTOST CPSPTOTT CPSATOFC
CPSATOST CPSATOTT CPST05FC CPST05ST CPST05TT CPSAINFC CPSAPAFC
CPSABLFC CPSAHIFC CPSAWHFC CPSAOEFC CPSTINFC CPSTPAFC CPSTBLFC
CPSTHIFC CPSTWHFC CPSTOEFC CPSTMAFC CPSTFEFC C_ENROLL CAVGNFTE
CPSTREFC CPSTVOFC CPSTBIFC CPSTCOFC CPSTGIFC CPSTSPFC CPSTOPFC
CPSTGOFC CPSOTOFC CPCTENGS CPCTMATS CPCTSCIS CPCTSOCS CPCTFLAS
CPCTGKGS CPCTG01S CPCTG02S CPCTG03S CPCTG04S CPCTG05S CPCTG06S
CPCTGMES CPCTENGC CPCTMATC CPCTSCIC CPCTSOCC CPCTFLAC CPCTGKGC
CPCTG01C CPCTG02C CPCTG03C CPCTG04C CPCTG05C CPCTG06C CPCTGMEC
CPSAMIFC CPSTEXPT CPSTTENT;
DO OVER ZS;
IF ZS = . THEN ZS = 0;
END;
FLAG = 0;
IF CPSATOFC = 0 & SUBSTR(CAMPUS,7,3) < '700' THEN FLAG = 1;
CPSAMIFC = SUM(CPSAOEFC,CPSABLFC,CPSAHIFC);
IF CPSTEXPA NE . THEN CPSTEXPT = CPSTEXPA * CAVGNFTE;
IF CPSTTENA NE . THEN CPSTTENT = CPSTTENA * CAVGNFTE;
SFLAG = 0; TFLAG = 0;
ARRAY P1 CPSTTOFP CPSUTOFP CPSSTOFP CPSETOFP CPSPTOFP CPSAMIFP;
ARRAY N1 CPSTTOFC CPSUTOFC CPSSTOFC CPSETOFC CPSPTOFC CPSAMIFC;
DO OVER P1;
IF CPSATOFC NE 0 THEN P1 = (N1/CPSATOFC)*100;
ELSE IF SUBSTR(CAMPUS,7,3) < '700' THEN DO;
SFLAG = 1;
P1 = 0;
END;
END;
ARRAY P2 CPST00FP CPST01FP CPST06FP CPST11FP CPST20FP CPSTREFP
CPSTVOFP CPSTBIFP CPSTCOFP CPSTGIFP CPSTSPFP CPSTOPFP
CPSTGOFP CPSTWHFP CPSTBLFP CPSTHIFP CPSTOEFP CPSTMAFP
CPSTFEFP CPSTINFP CPSTPAFP CPST05FP;
ARRAY N2 CPST00FC CPST01FC CPST06FC CPST11FC CPST20FC CPSTREFC
CPSTVOFC CPSTBIFC CPSTCOFC CPSTGIFC CPSTSPFC CPSTOPFC
CPSTGOFC CPSTWHFC CPSTBLFC CPSTHIFC CPSTOEFC CPSTMAFC
CPSTFEFC CPSTINFC CPSTPAFC CPST05FC;
DO OVER P2;
IF CPSTTOFC NE 0 THEN P2 = (N2/CPSTTOFC)*100;
ELSE IF SUBSTR(CAMPUS,7,3) < '700' THEN DO;
TFLAG = 1;
P2 = 0;
END;
END;
IF CPSTTOFC > 0 THEN CPSTKIDR = C_ENROLL/CPSTTOFC;
IF CPSATOFC > 0 THEN CPSAKIDR = C_ENROLL/CPSATOFC;
IF SUBSTR(CAMPUS,4,3) = '950' THEN OUTPUT NINE50S;
IF FLAG = 1 THEN OUTPUT MIS_FTE;
IF SFLAG = 1 THEN OUTPUT MIS_SFTE;
IF TFLAG = 1 THEN OUTPUT MIS_TFTE;
OUTPUT CSTAFF;
CALL SYMPUT('TODAY',PUT(TODAY(),DATE8.));
CALL SYMPUT('TIME ',PUT( TIME(),TOD5. ));
PROC SORT DATA = CSTAFF;
BY CAMPUS;
PROC PRINT DATA = NINE50S;
VAR CAMPUS CPSATOFC CPSTTOFC CPSUTOFC CPSSTOFC
CPSETOFC CPSPTOFC CPSAMIFC;
TITLE 'Service Center (950) Campuses on Campus Staff File';
FOOTNOTE1 "&DIVISION";
FOOTNOTE2 "&PROJECT";
FOOTNOTE3 "&AUTHOR.: &PROGRAM.: &TASK";
FOOTNOTE4 "&TODAY. &TIME.";
PROC PRINT DATA = MIS_FTE;
VAR CAMPUS CPSATOFC CPSTTOFC C_ENROLL;
TITLE 'Non-Admin Campus Staff w/Missing FTEs';
PROC PRINT DATA = MIS_SFTE;
VAR CAMPUS CPSATOFC CPSTTOFC CPSUTOFC CPSSTOFC
CPSETOFC CPSPTOFC CPSAMIFC;
TITLE 'Non-Admin Campuses without Staff FTEs';
PROC PRINT DATA = MIS_TFTE;
VAR CAMPUS CPST00FC CPST01FC CPST05FC CPST06FC CPST11FC CPST20FC
CPSTREFC CPSTVOFC CPSTBIFC CPSTCOFC CPSTGIFC CPSTSPFC
CPSTOPFC CPSTGOFC CPSTWHFC CPSTBLFC CPSTHIFC CPSTOEFC
CPSTMAFC CPSTFEFC CPSTINFC CPSTPAFC;
TITLE 'Non-Admin Campuses without Teacher FTEs';
*=================================*
* Campus Group Processing (BEVY) *
*------------------------------------------------------------------*;
%MACRO BREAK(GRP);
DATA BSTAFF&GRP;
MERGE CSTAFF (IN=A)
GROUPS.BEVY;
BY CAMPUS;
IF A AND GROUP = "&GRP";
PROC SORT;
BY TARGET;
PROC SUMMARY DATA = BSTAFF&GRP NWAY;
VAR _NUMERIC_;
BY TARGET;
OUTPUT OUT = BSTAFF&GRP (DROP = _TYPE_ _FREQ_) SUM =;
PROC TRANSPOSE DATA = BSTAFF&GRP OUT = BSTAFF&GRP; BY TARGET;
DATA BSTAFF&GRP;
SET BSTAFF&GRP;
IF SUBSTR(_NAME_,1,1) = 'C' THEN SUBSTR(_NAME_,1,1) = 'B';
PROC TRANSPOSE DATA = BSTAFF&GRP OUT = BSTAFF&GRP (DROP = _NAME_);
BY TARGET;
%MEND BREAK;
%BREAK(B)
%BREAK(S)
%BREAK(M)
%BREAK(E)
DATA BSTAFF;
SET BSTAFFE (RENAME = (TARGET = CAMPUS))
BSTAFFM (RENAME = (TARGET = CAMPUS))
BSTAFFS (RENAME = (TARGET = CAMPUS))
BSTAFFB (RENAME = (TARGET = CAMPUS));
ARRAY P1 BPSTTOFP BPSUTOFP BPSSTOFP BPSETOFP BPSPTOFP BPSAMIFP BPSAKIDR;
ARRAY N1 BPSTTOFC BPSUTOFC BPSSTOFC BPSETOFC BPSPTOFC BPSAMIFC B_ENROLL;
DO OVER P1;
IF BPSATOFC NE 0 THEN P1 = (N1/BPSATOFC)*100;
END;
ARRAY P2 BPST00FP BPST01FP BPST06FP BPST11FP BPST20FP BPSTREFP BPSTVOFP
BPSTBIFP BPSTCOFP BPSTGIFP BPSTSPFP BPSTOPFP BPSTGOFP BPSTWHFP
BPSTBLFP BPSTHIFP BPSTOEFP BPSTMAFP BPSTFEFP BPSTINFP BPSTPAFP
BPST05FP;
ARRAY N2 BPST00FC BPST01FC BPST06FC BPST11FC BPST20FC BPSTREFC BPSTVOFC
BPSTBIFC BPSTCOFC BPSTGIFC BPSTSPFC BPSTOPFC BPSTGOFC BPSTWHFC
BPSTBLFC BPSTHIFC BPSTOEFC BPSTMAFC BPSTFEFC BPSTINFC BPSTPAFC
BPST05FC;
DO OVER P2;
IF BPSTTOFC NE 0 THEN P2 = (N2/BPSTTOFC)*100;
END;
ARRAY P3 BPSTEXPA BPSTTENA BPSTKIDR BPSAKIDR BPSTTOSA BPSUTOSA BPSSTOSA
BPST00SA BPST01SA BPST06SA BPST11SA BPST20SA BPST05SA BPSTTOTA
BPSUTOTA BPSSTOTA BPST00TA BPST01TA BPST06TA BPST11TA BPST20TA
BPST05TA;
ARRAY N3 BPSTEXPT BPSTTENT B_ENROLL B_ENROLL BPSTTOST BPSUTOST BPSSTOST
BPST00ST BPST01ST BPST06ST BPST11ST BPST20ST BPST05ST BPSTTOTT
BPSUTOTT BPSSTOTT BPST00TT BPST01TT BPST06TT BPST11TT BPST20TT
BPST05TT;
ARRAY D3 BAVGNFTE BAVGNFTE BPSTTOFC BPSATOFC BPSTTOFC BPSUTOFC BPSSTOFC
BPST00FC BPST01FC BPST06FC BPST11FC BPST20FC BPST05FC BPSTTOFC
BPSUTOFC BPSSTOFC BPST00FC BPST01FC BPST06FC BPST11FC BPST20FC
BPST05FC;
DO OVER P3;
P3 = (N3/D3);
END;
ARRAY N4 BPCTENGS BPCTMATS BPCTSCIS BPCTSOCS BPCTFLAS BPCTGKGS BPCTGMES
BPCTG01S BPCTG02S BPCTG03S BPCTG04S BPCTG05S BPCTG06S;
ARRAY D4 BPCTENGC BPCTMATC BPCTSCIC BPCTSOCC BPCTFLAC BPCTGKGC BPCTGMEC
BPCTG01C BPCTG02C BPCTG03C BPCTG04C BPCTG05C BPCTG06C;
ARRAY P4 BPCTENGA BPCTMATA BPCTSCIA BPCTSOCA BPCTFLAA BPCTGKGA BPCTGMEA
BPCTG01A BPCTG02A BPCTG03A BPCTG04A BPCTG05A BPCTG06A;
DO OVER P4;
IF D4 NE 0 THEN P4 = ROUND((N4/D4),.1);
END;
PROC SORT;
BY CAMPUS;
*=================================*
* Combine Campus & Bevy *
*---------------------------------*;
DATA CAMPFIN;
LENGTH DISTRICT $6.;
* SET CSTAFF (IN=A);
MERGE CSTAFF (IN=A)
BSTAFF (IN=B);
BY CAMPUS;
IF B AND NOT A THEN PUT A= B= CAMPUS= DISTRICT=;
IF A;
DISTRICT = SUBSTR(CAMPUS,1,6);
*=================================*
* District Processing *
*------------------------------------------------------------------*;
PROC SQL;
CONNECT TO DB2;
CREATE TABLE WORK.DETHNIC AS
SELECT DISTRICT, ETHNIC, SUM(FTE) AS FTE
FROM CONNECTION TO DB2
(SELECT DISTRICT, ETHNIC, FTE
FROM P.STAFF_DEMOG&CY.F
WHERE ROLEGRP2 NOT IN ('11','12','13','14','19',' '))
GROUP BY DISTRICT, ETHNIC
ORDER BY DISTRICT;
DISCONNECT FROM DB2;
QUIT;
DATA DETHNIC (DROP = FTE ETHNIC);
SET DETHNIC;
%ETH(DPSA);
PROC SUMMARY DATA = DETHNIC NWAY;
VAR _NUMERIC_;
CLASS DISTRICT;
OUTPUT OUT = DETHNIC (DROP = _TYPE_ _FREQ_) SUM =;
PROC SQL;
CONNECT TO DB2;
CREATE TABLE WORK.DTCHETH AS
SELECT DISTRICT, SEX, ETHNIC, SUM(FTE) AS FTE
FROM CONNECTION TO DB2
(SELECT DISTRICT, SEX, ETHNIC, FTE
FROM P.STAFF_DEMOG&CY.F
WHERE ROLEGRP2 = '21')
GROUP BY DISTRICT, SEX, ETHNIC
ORDER BY DISTRICT;
DISCONNECT FROM DB2;
QUIT;
DATA DTCHETH (DROP = ETHNIC FTE);
SET DTCHETH;
%ETH(DPST);
IF SEX = 'M' THEN DPSTMAFC = FTE;
IF SEX = 'F' THEN DPSTFEFC = FTE;
PROC SUMMARY DATA = DTCHETH NWAY;
VAR _NUMERIC_;
CLASS DISTRICT;
OUTPUT OUT = DTCHETH (DROP = _TYPE_ _FREQ_) SUM=;
PROC SQL;
CONNECT TO DB2;
CREATE TABLE WORK.DAUX AS
SELECT DISTRICT, ETHNIC, SUM(FTE) AS FTE
FROM CONNECTION TO DB2
(SELECT DISTRICT, ETHNIC, FTE
FROM P.AUXILIARY&CY.F)
GROUP BY DISTRICT, ETHNIC
ORDER BY DISTRICT;
DISCONNECT FROM DB2;
QUIT;
DATA AUXETH (DROP = FTE ETHNIC);
SET DAUX;
%ETH(PSA);
PROC SUMMARY DATA = AUXETH NWAY;
VAR _NUMERIC_;
CLASS DISTRICT;
OUTPUT OUT = AUXETH (DROP = _TYPE_ _FREQ_) SUM =;
*=================================*
* Staff & Aux. Ethnicity Combined *
*------------------------------------------------------------------*;
DATA DETHNIC (DROP = PSAOEFC PSAWHFC PSABLFC PSAHIFC PSAINFC PSAPAFC);
MERGE DETHNIC (IN=A)
AUXETH;
BY DISTRICT;
DPSAOEFC = SUM(DPSAOEFC,PSAOEFC);
DPSAINFC = SUM(DPSAINFC,PSAINFC);
DPSAPAFC = SUM(DPSAPAFC,PSAPAFC);
DPSAWHFC = SUM(DPSAWHFC,PSAWHFC);
DPSABLFC = SUM(DPSABLFC,PSABLFC);
DPSAHIFC = SUM(DPSAHIFC,PSAHIFC);
PROC DATASETS;
DELETE DAUX;
RUN;
PROC SQL;
CONNECT TO DB2;
CREATE TABLE WORK.DAUX AS
SELECT DISTRICT, SUM(FTE) AS DPSXTOFC, SUM(TOTALPAY) AS DPSXTOTT
FROM CONNECTION TO DB2
(SELECT DISTRICT, FTE, TOTALPAY
FROM P.AUXILIARY&CY.F)
GROUP BY DISTRICT
ORDER BY DISTRICT;
CREATE TABLE WORK.CAMPCONT AS
SELECT CAMPUS, SUM(CONTRINSTR_FTE) AS DPSOTOFC
FROM CONNECTION TO DB2
(SELECT CAMPUS, CONTRINSTR_FTE
FROM P.CONTRACT_STAFF&CY.F)
GROUP BY CAMPUS
ORDER BY CAMPUS;
DISCONNECT FROM DB2;
QUIT;
DATA REGROUP;
LENGTH DISTRICT $6.;
SET CAMPCONT;
DISTRICT = SUBSTR(CAMPUS,1,6);
PROC SORT;
BY DISTRICT;
PROC SUMMARY DATA = REGROUP NWAY;
VAR DPSOTOFC;
CLASS DISTRICT;
OUTPUT OUT = CONTRACT (DROP = _TYPE_ _FREQ_) SUM =;
PROC SQL;
CONNECT TO DB2;
CREATE TABLE WORK.COOPAUX AS
SELECT *
FROM CONNECTION TO DB2
(SELECT A.BASEPAY, B.PAYAMT, A.FTE, A.PERSONID, A.DISTRICT
FROM P.EMPLOY&CY.F AS A,
P.PAY&CY.F AS B
WHERE A.PERSONID = B.PERSONID AND STAFFGRP1 = '05'
AND (FUND LIKE '29%' OR FUND LIKE '30%' OR FUND LIKE '31%' OR
FUND LIKE '32%' OR FUND LIKE '33%' OR FUND LIKE '34%' OR
FUND LIKE '35%' OR FUND LIKE '36%' OR FUND LIKE '37%' OR
FUND LIKE '43%' OR FUND LIKE '44%' OR FUND LIKE '45%'))
ORDER BY DISTRICT;
DISCONNECT FROM DB2;
QUIT;
PROC SUMMARY DATA = COOPAUX NWAY;
VAR PAYAMT;
CLASS PERSONID;
ID DISTRICT FTE BASEPAY;
OUTPUT OUT = COOPAUX (DROP = _TYPE_ _FREQ_) SUM= ;
DATA COOPAUX;
SET COOPAUX;
IF BASEPAY > 0 THEN DPSXCOFC = FTE*(PAYAMT/BASEPAY);
PROC SUMMARY DATA = COOPAUX NWAY;
VAR DPSXCOFC;
CLASS DISTRICT;
OUTPUT OUT = DCAUX (DROP = _TYPE_ _FREQ_) SUM= ;
PROC SQL;
CONNECT TO DB2;
CREATE TABLE WORK.DISTAGG AS
SELECT *
FROM CONNECTION TO DB2
(SELECT A.PERSONID, A.EXPER, A.TOTALPAY, A.BASEPAY, A.DEGREE,
B.DISTRICT, B.PFTE, B.PTIME, B.ROLEGRP2, B.ROLE
FROM P.EMPLOY&CY.F AS A,
P.STAFF_ROLE&CY.F AS B
WHERE A.PERSONID = B.PERSONID
AND A.DISTRICT = B.DISTRICT)
ORDER BY DISTRICT;
DISCONNECT FROM DB2;
QUIT;
DATA DISTAGG (DROP = ROLEGRP2 PFTE PTIME BASEPAY TOTALPAY EXPER
U1-U5 L1-L5);
SET DISTAGG;
IF EXPER = . THEN EXPER = 0;
ARRAY GRPS $ G1-G5 ('21','22','23','24','25');
ARRAY FTES DPSTTOFC DPSUTOFC DPSSTOFC DPSCTOFC DPSETOFC;
ARRAY BPAY DPSTTOST DPSUTOST DPSSTOST DPSCTOST DPSETOST;
ARRAY TPAY DPSTTOTT DPSUTOTT DPSSTOTT DPSCTOTT DPSETOTT;
DO OVER GRPS;
IF ROLEGRP2 = GRPS THEN DO;
FTES = PFTE;
BPAY = PTIME*BASEPAY;
TPAY = PTIME*TOTALPAY;
END;
END;
ARRAY ULS U1-U5 ( 1, 6, 11, 21, 99);
ARRAY LLS L1-L5 (-1, 0, 5, 10, 20);
ARRAY EFTES DPST00FC DPST01FC DPST06FC DPST11FC DPST20FC;
ARRAY EBPAY DPST00ST DPST01ST DPST06ST DPST11ST DPST20ST;
ARRAY ETPAY DPST00TT DPST01TT DPST06TT DPST11TT DPST20TT;
DO OVER EFTES;
IF ROLEGRP2 = '21' & (LLS < EXPER < ULS) THEN DO;
EFTES = PFTE;
EBPAY = PTIME*BASEPAY;
ETPAY = PTIME*TOTALPAY;
END;
END;
DPST05FC = SUM(DPST00FC,DPST01FC);
IF ROLEGRP2 = '21' THEN DO;
IF DEGREE = '0' THEN DPSTNOFC = PFTE;
IF DEGREE = '1' THEN DPSTBAFC = PFTE;
IF DEGREE = '2' THEN DPSTMSFC = PFTE;
IF DEGREE = '3' THEN DPSTPHFC = PFTE;
END;
IF ROLEGRP2 IN ('11','12','13') THEN DPSPCOFC = PFTE;
IF ROLEGRP2 = '14' THEN DPSECOFC = PFTE;
DPST05FC = SUM(DPST00FC,DPST01FC);
DPST05ST = SUM(DPST00ST,DPST01ST);
DPST05TT = SUM(DPST00TT,DPST01TT);
DPSPTOFC = SUM(DPSTTOFC,DPSSTOFC,DPSUTOFC,DPSCTOFC);
DPSPTOTT = SUM(DPSTTOTT,DPSSTOTT,DPSUTOTT,DPSCTOTT);
DPSPTOST = SUM(DPSTTOST,DPSSTOST,DPSUTOST,DPSCTOST);
PROC SUMMARY DATA = DISTAGG NWAY;
VAR _NUMERIC_;
CLASS DISTRICT;
OUTPUT OUT = DISTAGG (DROP = _TYPE_ _FREQ_) SUM =;
DATA DSTAFF;
MERGE DISTAGG (IN=A)
DAUX;
BY DISTRICT;
IF A;
DPSXTOST = DPSXTOTT; * <--- Aux Base = Aux Total *;
DPSATOFC = SUM(DPSPTOFC,DPSETOFC,DPSXTOFC);
DPSATOTT = SUM(DPSPTOTT,DPSETOTT,DPSXTOTT);
DPSATOST = SUM(DPSPTOST,DPSETOST,DPSXTOST);
*=================================*
* Combine District Datasets *
*------------------------------------------------------------------*;
DATA DSTAFF;
MERGE DSTAFF (IN=DS)
CONTRACT
DETHNIC
DTCHETH
DCAUX
DPROG
TURNOVER.FINAL (KEEP = DISTRICT PSTURNR PSTURNN PSTURND
RENAME = (PSTURNR = DPSTURNR PSTURNN = DPSTURNN
PSTURND = DPSTURND) IN=T)
TENURE.DISTRICT (RENAME = (FTE = DAVGNFTE)
DROP = WGTTEN WGTEXP);
BY DISTRICT;
ARRAY ZS DPSTTOFC DPSUTOFC DPSSTOFC DPSCTOFC DPSETOFC DPSTTOST
DPSUTOST DPSSTOST DPSCTOST DPSETOST DPSTTOTT DPSUTOTT DPSSTOTT
DPSCTOTT DPSETOTT DPST00FC DPST01FC DPST06FC DPST11FC DPST20FC
DPST00ST DPST01ST DPST06ST DPST11ST DPST20ST DPST00TT DPST01TT
DPST06TT DPST11TT DPST20TT DPST05FC DPSTNOFC DPSTBAFC DPSTMSFC
DPSTPHFC DPSPCOFC DPSECOFC DPST05ST DPST05TT DPSPTOFC DPSPTOTT
DPSPTOST
DPSXTOFC DPSXTOTT DPSXTOST DPSATOFC DPSATOTT DPSATOST D_ENROLL
DPSOTOFC DPSAINFC DPSAPAFC DPSABLFC DPSAHIFC DPSAWHFC DPSAOEFC
DPSTINFC DPSTPAFC DPSTBLFC DPSTHIFC DPSTWHFC DPSTOEFC DPSTMAFC
DPSTFEFC DPSXCOFC DPSTREFC DPSTVOFC DPSTBIFC DPSTCOFC DPSTGIFC
DPSTSPFC DPSTOPFC DPSTGOFC DPSTURNN DPSTURND DAVGNFTE DPSTADFC
DPSTOTPT DPSAMIFC DPSTEXPT DPSTTENT DPCTSOCS DPCTSOCC DPCTSCIS
DPCTSCIC DPCTMATS DPCTMATC DPCTG06S DPCTG06C DPCTG05S DPCTG05C
DPCTG04S DPCTG04C DPCTG03S DPCTG03C DPCTG02S DPCTG02C DPCTG01S
DPCTG01C DPCTGMES DPCTGMEC DPCTGKGS DPCTGKGC DPCTFLAS DPCTFLAC
DPCTENGS DPCTENGC;
DO OVER ZS;
IF ZS = . THEN ZS = 0;
END;
PROC SQL;
CONNECT TO DB2;
CREATE TABLE WORK.DREGNS AS
SELECT *
FROM CONNECTION TO DB2
(SELECT DISTRICT, DISTNAME, REGION
FROM P.DISTRICT04F)
ORDER BY DISTRICT;
DISCONNECT FROM DB2;
QUIT;
PROC SORT DATA = DREGNS;
BY DISTRICT;
DATA DSTAFF
NOTONCV (KEEP = DISTRICT DPSTTOFC DPSUTOFC DPSSTOFC DPSCTOFC
DPSOTOFC DPSETOFC DPSPTOFC DPSXTOFC DPSATOFC DPSXCOFC)
NINE50S (KEEP = DISTRICT DPSTTOFC DPSUTOFC DPSSTOFC DPSCTOFC
DPSOTOFC DPSETOFC DPSPTOFC DPSXTOFC DPSATOFC DPSXCOFC);
MERGE DSTAFF (IN=DS)
DREGNS
COMMON.DISTRICT (IN=A KEEP = DISTRICT DPETALLC
RENAME = (DPETALLC = D_ENROLL));
BY DISTRICT;
*------------------------------------------------*
* Staff should not contain ESC data. Check to *
* insure that Staff data is not lost due to *
* common variable file restriction. *
*------------------------------------------------*;
IF DS & SUBSTR(DISTRICT,4,3) = '950' THEN OUTPUT NINE50S;
IF DS & NOT A THEN OUTPUT NOTONCV;
IF (A & DS) | (DS & SUBSTR(DISTRICT,4,3) NE '950');
DPSTADFC = SUM(DPSTMSFC,DPSTPHFC);
DPSTOTPT = SUM(DPSTNRPC,DPSTTEPC);
DPSAMIFC = SUM(DPSAOEFC,DPSABLFC,DPSAHIFC);
DPSTEXPT = DPSTEXPA * DAVGNFTE;
DPSTTENT = DPSTTENA * DAVGNFTE;
OUTPUT DSTAFF;
PROC PRINT DATA = NOTONCV;
VAR DISTRICT DPSATOFC DPSTTOFC DPSUTOFC DPSSTOFC
DPSETOFC DPSPTOFC;
TITLE 'Districts w/Staff Data - Not on Common Variables File';
PROC PRINT DATA = NINE50S;
VAR DISTRICT DPSATOFC DPSTTOFC DPSUTOFC DPSSTOFC
DPSETOFC DPSPTOFC;
TITLE 'Service Centers on District Staff Data';
*=================================*
* Summarize to Region Level *
*---------------------------------*;
PROC SORT DATA = DSTAFF;
BY REGION;
PROC SUMMARY DATA = DSTAFF;
VAR _NUMERIC_;
BY REGION;
OUTPUT OUT = RSTAFF (DROP = _TYPE_ _FREQ_) SUM=;
PROC TRANSPOSE DATA = RSTAFF OUT = RSTAFF; BY REGION;
DATA RSTAFF;
SET RSTAFF;
IF SUBSTR(_NAME_,1,1) = 'D' THEN SUBSTR(_NAME_,1,1) = 'R';
PROC TRANSPOSE DATA = RSTAFF OUT = RSTAFF (DROP = _NAME_); BY REGION;
*=================================*
* Summarize to State Level *
*-----------------------------------------------------------------*;
PROC SUMMARY DATA = DSTAFF;
VAR _NUMERIC_;
OUTPUT OUT = SSTAFF (DROP = _TYPE_ _FREQ_) SUM=;
PROC TRANSPOSE DATA = SSTAFF OUT = SSTAFF;
DATA SSTAFF;
SET SSTAFF;
IF SUBSTR(_NAME_,1,1) = 'D' THEN SUBSTR(_NAME_,1,1) = 'S';
PROC TRANSPOSE DATA = SSTAFF OUT = SSTAFF (DROP = _NAME_);
%MACRO ARR1(E,ECH);
ARRAY P1 &E.PSPTOFP &E.PSTTOFP &E.PSUTOFP &E.PSSTOFP &E.PSCTOFP
&E.PSETOFP &E.PSXTOFP &E.PSAMIFP &E.PSAKIDR;
ARRAY N1 &E.PSPTOFC &E.PSTTOFC &E.PSUTOFC &E.PSSTOFC &E.PSCTOFC
&E.PSETOFC &E.PSXTOFC &E.PSAMIFC &E._ENROLL;
DO OVER P1;
IF &E.PSATOFC NE . & &E.PSATOFC NE 0 THEN DO;
IF &E.PSATOFC NE 0 THEN P1 = (N1/&E.PSATOFC)*100;
END;
ELSE DO;
PUT &ECH = &E.PSATOFC = N1 =;
P1 = 0;
END;
END;
%MEND ARR1;
%MACRO FTCP(L,S);
&L.PSTNOF&S &L.PSTBAF&S &L.PSTMSF&S &L.PSTPHF&S &L.PST00F&S &L.PST01F&S
&L.PST06F&S &L.PST11F&S &L.PST20F&S &L.PSTREF&S &L.PSTVOF&S &L.PSTBIF&S
&L.PSTCOF&S &L.PSTGIF&S &L.PSTSPF&S &L.PSTOPF&S &L.PSTGOF&S &L.PSTBLF&S
&L.PSTWHF&S &L.PSTHIF&S &L.PSTOEF&S &L.PSTMAF&S &L.PSTFEF&S &L.PSTINF&S
&L.PSTPAF&S &L.PST05F&S &L.PSTADF&S
%MEND FTCP;
%MACRO ARR2(E,ECH);
ARRAY P2 %FTCP(&E,P);
ARRAY N2 %FTCP(&E,C);
DO OVER P2;
IF &E.PSTTOFC NE . & &E.PSTTOFC NE 0 THEN DO;
IF &E.PSTTOFC NE 0 THEN P2 = (N2/&E.PSTTOFC)*100;
END;
ELSE DO;
PUT &ECH = N2 =;
P2 = 0;
END;
END;
IF &E.PSTTOFC > 0 THEN &E.PSTKIDR = &E._ENROLL/&E.PSTTOFC;
IF &E.PSATOFC > 0 THEN &E.PSAKIDR = &E._ENROLL/&E.PSATOFC;
ELSE &E.PSAKIDR = 0;
IF &E.PSTURND NE 0 THEN &E.PSTURNR = (&E.PSTURNN/&E.PSTURND)*100;
%MEND ARR2;
%MACRO ARR3(E);
ARRAY P3 &E.PSTEXPA &E.PSTTENA &E.PST00SA &E.PST01SA &E.PST06SA
&E.PST11SA &E.PST20SA &E.PSTTOSA &E.PSUTOSA &E.PSSTOSA &E.PSCTOSA
&E.PSTKIDR &E.PST05SA &E.PSAKIDR &E.PST00TA &E.PST01TA &E.PST06TA
&E.PST11TA &E.PST20TA &E.PSTTOTA &E.PSUTOTA &E.PSSTOTA &E.PSCTOTA
&E.PST05TA &E.PSPTOSA &E.PSATOSA &E.PSETOSA &E.PSXTOSA &E.PSPTOTA
&E.PSATOTA &E.PSETOTA &E.PSXTOTA;
ARRAY N3 &E.PSTEXPT &E.PSTTENT &E.PST00ST &E.PST01ST &E.PST06ST
&E.PST11ST &E.PST20ST &E.PSTTOST &E.PSUTOST &E.PSSTOST &E.PSCTOST
&E._ENROLL &E.PST05ST &E._ENROLL &E.PST00TT &E.PST01TT &E.PST06TT
&E.PST11TT &E.PST20TT &E.PSTTOTT &E.PSUTOTT &E.PSSTOTT &E.PSCTOTT
&E.PST05TT &E.PSPTOST &E.PSATOST &E.PSETOST &E.PSXTOST &E.PSPTOTT
&E.PSATOTT &E.PSETOTT &E.PSXTOTT;
ARRAY D3 &E.AVGNFTE &E.AVGNFTE &E.PST00FC &E.PST01FC &E.PST06FC
&E.PST11FC &E.PST20FC &E.PSTTOFC &E.PSUTOFC &E.PSSTOFC &E.PSCTOFC
&E.PSTTOFC &E.PST05FC &E.PSATOFC &E.PST00FC &E.PST01FC &E.PST06FC
&E.PST11FC &E.PST20FC &E.PSTTOFC &E.PSUTOFC &E.PSSTOFC &E.PSCTOFC
&E.PST05FC &E.PSPTOFC &E.PSATOFC &E.PSETOFC &E.PSXTOFC &E.PSPTOFC
&E.PSATOFC &E.PSETOFC &E.PSXTOFC;
DO OVER P3;
IF D3 NE 0 THEN P3 = (N3/D3);
END;
%MEND ARR3;
*=================================*
* District Percentages *
*-----------------------------------------------------------------*;
PROC SORT DATA = DSTAFF; BY DISTRICT;
DATA DSTAFF;
* SET DSTAFF (IN=A);
MERGE DSTAFF (IN=A)
CLASS.DISTRICT (KEEP = DISTRICT %CLASS(D));
BY DISTRICT;
IF A;
%ARR1(D,DISTRICT);
%ARR2(D,DISTRICT);
%ARR3(D);
*=================================*
* Region Percentages *
*---------------------------------*;
DATA RSTAFF;
* SET RSTAFF (IN=A);
MERGE RSTAFF (IN=A)
CLASS.REGION (KEEP = REGION %CLASS(R));
BY REGION;
IF A;
%ARR1(R,REGION);
%ARR2(R,REGION);
%ARR3(R);
*=================================*
* State Percentages *
*-----------------------------------------------------------------*;
DATA SSTAFF;
* SET SSTAFF;
MERGE SSTAFF
CLASS.STATE (KEEP = %CLASS(S));
STATE = 'STATE';
%ARR1(S,STATE);
%ARR2(S,STATE);
%ARR3(S);
*=================================*
* District, Region, & State Vars *
* appended to Campus Final *
*---------------------------------*;
PROC SQL;
CREATE TABLE CAMPFINL AS
SELECT *
FROM CAMPFIN
ORDER BY DISTRICT;
PROC SQL;
CREATE TABLE DSTAFFT AS
SELECT *
FROM DSTAFF
ORDER BY DISTRICT;
DATA CAMPFINL BLANK2 CMPONLY;
MERGE CAMPFINL (IN=B)
DSTAFFT (IN=A)
COMMON.EXCLUDE (IN=C KEEP = DISTRICT);
BY DISTRICT;
IF B & NOT C THEN OUTPUT CAMPFINL;
IF B & NOT A THEN OUTPUT CMPONLY;
IF A & NOT B THEN OUTPUT BLANK2;
PROC PRINT DATA = BLANK2;
VAR CAMPUS CAMPNAME CPSATOFC CPSTTOFC CPSUTOFC CPSSTOFC
CPSETOFC CPSPTOFC CPSAMIFC;
TITLE 'Dump of Blank2';
PROC PRINT DATA = CMPONLY;
VAR CAMPUS CAMPNAME CPSATOFC CPSTTOFC CPSUTOFC CPSSTOFC
CPSETOFC CPSPTOFC CPSAMIFC;
TITLE 'Dump of Campuses on Campfinl but not Dstafft';
PROC SORT DATA = CAMPFINL; BY REGION;
PROC SORT DATA = RSTAFF;
BY REGION;
DATA CAMPFINL;
MERGE CAMPFINL (IN=A)
RSTAFF;
BY REGION;
IF A;
IF _N_ = 1 THEN SET SSTAFF (DROP=STATE);
PROC SORT;
BY CAMPUS;
*=================================*
* Labeling Methodology *
*---------------------------------*;
DATA FIRSTN (DROP = CAMPNAME DISTRICT REGION)
FIRSTC (KEEP = CAMPUS CAMPNAME DISTRICT REGION) REST;
SET CAMPFINL;
IF _N_ = 1 THEN OUTPUT FIRSTN;
IF _N_ = 1 THEN OUTPUT FIRSTC;
ELSE IF _N_ > 1 THEN OUTPUT REST;
PROC TRANSPOSE DATA = FIRSTN OUT = TEST; BY CAMPUS;
DATA TEST (KEEP = CAMPUS _NAME_ _LABEL_ COL1);
LENGTH J1-J7 L1-L4 $11 B1-B5 D1-D9 $13 F1-F37 $31 H1-H5 N1-N6 $16
_LABEL_ $120;
SET TEST;
* Class size variables arrive w/own labels - only need to change
* for the group class size variables from "campus" to "group" ;
IF SUBSTR(_NAME_,2,3) = 'PCT' THEN DO;
IF SUBSTR(_NAME_,1,1) = 'B' THEN
_LABEL_ = 'Group'||SUBSTR(_LABEL_,7,113);
END;
ELSE DO;
ARRAY AS(I) $ A1-A5 ('C','B','D','R','S');
ARRAY BS(I) $ B1-B5 ("Campus &CFY","Group &CFY","District &CFY",
"Region &CFY","State &CFY");
ARRAY CS(J) $ C1-C9 ('T','S','C','U','E','X','P','A','O');
ARRAY DS(J) $ D1-D9 ('Teacher','School Admin','Central Admin','Support',
'Educ Aide','Auxiliary','Professional','All Staff',
'Contract Serv');
ARRAY ES(K) $ E1-E37 ('MA','FE','BL','HI','WH','OE','OP','CO','MI','TO',
'NO','BA','MS','PH','00','01','06','11','20','CA','EC',
'TE','NR','RE','SP','BI','GI','OP','GO','IN','PA','VO',
'05','AD','EU','DT','OT');
ARRAY FS(K) $ F1-F37 ('Male','Female','African American','Hispanic',
'White','Nat Amer + As/Pac Is','Other Program',
'Compensatory Program','Minority','Total','No Degree',
'BA Degree','MS Degree','PH Degree','Beginning',
'1-5 Years','6-10 Years','11-20 Years','> 20 Years',
'Temp Assign','Emerg Cert','Temp Exemp','Non-Renewable',
'Regular Program','Special Program','Bilingual Program',
'Gifted & Talented Program','Other',
'Gifted & Talented Program/Other','Native American',
'Asian/Pacific Islander','Career & Tech Prgms',
'0-5 Years','Advanced Degree','Emergency Uncertified',
'District Teaching','Other');
ARRAY GS(L) $ G1-G5 ('F','S','P','T','Y');
ARRAY HS(L) $ H1-H5 ('Full Time Equiv','Base Salary','Permit',
'Base w/Supp. Salary','Years');
ARRAY IS(M) $ I1-I7 ('C','P','A','T','R','D','N');
ARRAY JS(M) $ J1-J7 ('Count','Percent','Average','Total','Ratio',
'Denominator','Numerator');
ARRAY KS(N) $ K1-K4 ('EXP','TEN','KID','URN');
ARRAY LS(N) $ L1-L4 ('Experience','Tenure','Student','Turnover');
ARRAY MS(N) $ M1-M6 ('FL','SS','SC','CS','MA','EN');
ARRAY NS(N) $ N1-N6 ('Foreign Language','Social Studies','Science',
'Computer Science','Mathematics','English');
DO OVER AS;
IF SUBSTR(_NAME_,1,1) = AS & SUBSTR(_NAME_,2,7) = '_ENROLL' THEN
_LABEL_ = TRIM(BS)||' Staff: Total Enrollment';
END;
DO OVER AS;
DO OVER CS;
DO OVER ES;
DO OVER GS;
DO OVER IS;
IF SUBSTR(_NAME_,1,1) = AS & SUBSTR(_NAME_,4,1) = CS &
SUBSTR(_NAME_,5,2) = ES & SUBSTR(_NAME_,7,1) = GS &
SUBSTR(_NAME_,8,1) = IS THEN
_LABEL_ = TRIM(BS)||' Staff: '||TRIM(DS)||' '||TRIM(FS)||' '||
TRIM(HS)||' '||TRIM(JS);
END;
END;
END;
DO OVER KS;
DO OVER IS;
IF SUBSTR(_NAME_,1,1) = AS & SUBSTR(_NAME_,4,1) = CS &
SUBSTR(_NAME_,5,3) = KS & SUBSTR(_NAME_,8,1) = IS THEN
_LABEL_ = TRIM(BS)||' Staff: '||TRIM(DS)||' '||
TRIM(LS)||' '||TRIM(JS);
IF SUBSTR(_NAME_,1,1) = AS & SUBSTR(_NAME_,2,7) = 'AVGAFTE' THEN
_LABEL_ = TRIM(BS)||' Staff: '||
'Campus Admin Total Full Time Equiv - Experience Calculation';
IF SUBSTR(_NAME_,1,1) = AS & SUBSTR(_NAME_,2,7) = 'AVGNFTE' THEN
_LABEL_ = TRIM(BS)||' Staff: '||
'Teacher Total Full Time Equiv - Experience Calculation';
END;
END;
END;
END;
DO OVER AS;
IF SUBSTR(_NAME_,1,1) = AS & SUBSTR(_NAME_,5,4) = 'COFC' THEN DO;
IF SUBSTR(_NAME_,3,2) = 'SP' THEN
_LABEL_ = TRIM(BS)||' Staff: '||
'SSA Professional Staff Full Time Equiv Count';
IF SUBSTR(_NAME_,3,2) = 'SE' THEN
_LABEL_ = TRIM(BS)||' Staff: '||
'SSA Educational Aides Full Time Equiv Count';
IF SUBSTR(_NAME_,3,2) = 'SX' THEN
_LABEL_ = TRIM(BS)||' Staff: '||
'SSA Auxiliary Staff Full Time Equiv Count';
END;
END;
END;
PROC TRANSPOSE DATA = TEST OUT = ORIG; BY CAMPUS;
DATA ORIG;
MERGE ORIG
FIRSTC;
DATA STAFF.CAMPUS (DROP = _NAME_);
SET ORIG REST;
PROC SORT;
BY CAMPUS;
PROC PRINT DATA = staff.CAMPUS (OBS=20);
TITLE 'Staff Build - Campus Level (1st 20 Obs)';
PROC CONTENTS DATA = staff.CAMPUS;
TITLE 'Contents of Campus Staff Build Data Set';
PROC SORT DATA = staff.CAMPUS OUT = CAMPUS TAGSORT;
BY DISTRICT;
DATA FIRSTN (DROP = DISTNAME REGION)
FIRSTC (KEEP = DISTRICT DISTNAME REGION);
SET CAMPUS;
BY DISTRICT;
IF FIRST.DISTRICT & SUBSTR(DISTRICT,4,3) NE '950';
PROC TRANSPOSE DATA = FIRSTN OUT = FIRSTN;
BY DISTRICT;
DATA FIRSTN;
SET FIRSTN;
IF SUBSTR(_NAME_,1,1) IN ('B','C') THEN DELETE;
PROC TRANSPOSE DATA = FIRSTN
OUT = FIRSTN (DROP = _NAME_);
BY DISTRICT;
DATA DISTRICT;
MERGE FIRSTN
FIRSTC;
BY DISTRICT;
PROC SORT
DATA = STAFF.D_PCT (KEEP = DISTRICT_NUMBER TOTAL_OF_PERCENTINSTRHOURS
RENAME = (DISTRICT_NUMBER = DISTRICT
TOTAL_OF_PERCENTINSTRHOURS = DPSAINHP))
OUT = DIST_INSTHRS_PCTS;
BY DISTRICT;
RUN;
DATA STAFF.DISTRICT;
MERGE DISTRICT (IN = A)
DIST_INSTHRS_PCTS (IN = B);
BY DISTRICT;
IF A = 1;
LABEL DPSAINHP = "District &CFY Staff: Percent Of Instructional Hours";
RUN;
PROC CONTENTS DATA = STAFF.DISTRICT;
TITLE 'Contents of District Staff Build Data Set';
PROC PRINT DATA = STAFF.DISTRICT (OBS=20);
TITLE 'District Staff Data Set (1st 20 Obs)';
PROC SORT DATA = STAFF.DISTRICT OUT = REGONLY; BY REGION;
DATA REGONLY;
SET REGONLY;
BY REGION;
IF FIRST.REGION;
PROC TRANSPOSE DATA = REGONLY OUT = SETR; BY REGION;
DATA EXTRACT;
SET SETR;
IF SUBSTR(_NAME_,1,1) = 'R';
PROC TRANSPOSE DATA = EXTRACT
OUT = REGION (DROP = _NAME_);
BY REGION;
PROC SORT
DATA = STAFF.R_PCT (KEEP = TOTAL_OF_PERCENTINSTRHOURS REGION
RENAME = (TOTAL_OF_PERCENTINSTRHOURS = RPSAINHP))
OUT = REG_INSTHRS_PCTS;
BY REGION;
RUN;
DATA STAFF.REGION;
MERGE REGION (IN = A)
REG_INSTHRS_PCTS (IN = B);
BY REGION;
IF A = 1;
LABEL RPSAINHP = "Region &CFY Staff: Percent Of Instructional Hours";
RUN;
DATA STAONLY;
SET REGONLY;
IF _N_ = 1;
PROC TRANSPOSE DATA = STAONLY OUT = SETS;
DATA EXTRACT;
SET SETS;
IF SUBSTR(_NAME_,1,1) = 'S';
PROC TRANSPOSE DATA = EXTRACT
OUT = STATE (DROP = _NAME_);
DATA ST_PCTS_FINAL (KEEP = SPSAINHP);
SET STAFF.S_PCT (KEEP = PERCENTINSTRHOURS) NOBS = NOBS;
SPSAINHP + PERCENTINSTRHOURS;
IF NOBS = _N_ THEN OUTPUT;
RUN;
DATA STAFF.STATE;
SET STATE;
SET ST_PCTS_FINAL (KEEP = SPSAINHP);
LABEL SPSAINHP = "State &CFY Staff: Percent Of Instructional Hours";
RUN;
***********************************************************************;
PROC SORT
DATA = STAFF.CAMPUS
OUT = CAMPUS ;
BY DISTRICT ;
RUN;
DATA CAMPUS ;
MERGE CAMPUS (IN = CAMP)
DIST_INSTHRS_PCTS (IN = DIST);
BY DISTRICT ;
IF CAMP THEN OUTPUT ;
RUN ;
PROC SORT
DATA = CAMPUS ;
BY REGION ;
RUN;
DATA CAMPUS ;
MERGE CAMPUS (IN = CAMP)
REG_INSTHRS_PCTS (IN = REGI);
BY DISTRICT ;
IF CAMP THEN OUTPUT ;
RUN ;
PROC SQL ;
CREATE TABLE STAFF.CAMPUS AS
SELECT CAMP.* , STATE.*
FROM CAMPUS AS CAMP , ST_PCTS_FINAL AS STATE
ORDER BY CAMPUS , DISTRICT , REGION ;
QUIT ;
PROC SORT
DATA = STAFF.DISTRICT
OUT = DISTRICT ;
BY REGION ;
RUN ;
DATA DISTRICT ;
MERGE DISTRICT (IN = DIST)
REG_INSTHRS_PCTS (IN = REGI) ;
BY REGION ;
IF DIST THEN OUTPUT;
RUN;
PROC SQL ;
CREATE TABLE STAFF.DISTRICT AS
SELECT DIST.* , STATE.*
FROM DISTRICT AS DIST , ST_PCTS_FINAL AS STATE
ORDER BY DISTRICT , REGION ;
QUIT ;
DATA REGION ;
SET STAFF.REGION ;
RUN ;
PROC SQL ;
CREATE TABLE STAFF.REGION AS
SELECT REGI.* , STATE.*
FROM REGION AS REGI , ST_PCTS_FINAL AS STATE
ORDER BY REGION ;
QUIT ;
//
Toby Dunn
"It's OK to figure out murder mysteries, but you shouldn't need to
figure out code. You should be able to read it." -Steve C McConnell
-----Original Message-----
From: iw1junk@comcast.net [mailto:iw1junk@comcast.net]
Sent: Friday, December 10, 2004 1:00 PM
To: SAS(r) Discussion
Cc: Dunn, Toby
Subject: Re: How to define a global determistic variable without
using Macro
Toby,
First of all if your typical program is over 1000 lines of open
code, don't worry too much about global issues, you have bigger
problems. One of the reasons for macros is to impose structure
on large programs.
Your example of a four step program to represent a large program
does make it hard to see the point. As given VAR_LIST is really
being used as a program parameter. The only quarrel I have with
it is a name that might easily be used by anyone want to make a
list of variables at any time.
I would prefer something like CONSTANT_WORK_FOO_NUM_LIST as less
likely to be changed in the heat of adding a new section.
I would consider a macro to hide the variable.
%macro foolist ;
&CONSTANT_WORK_FOO_NUM_LIST
%mend foolist ;
then
title "Numeric FOO variables before treatment";
proc print
data = foo;
var %foolist ;
run;
I do not see why the list of variables should be in the title
when they appear as column headings in the report.
However, I would be more likely to structure the code as
%macro mklist ( data= , type= , exclude= , vlist= ) ; ...
%macro foolist ( ); ...
%macro lkbefore ( data= , vars = ) ; ...
%macro mods ( ... ) ; ...
%macro lkafter ( data= , vars= ) ; ...
%macro driver ( ... ) ;
%local CONSTANT_WORK_FOO_NUM_LIST /* do not modify */ ;
%mklist ( data=foo, type=num
, vlist=CONSTANT_WORK_FOO_NUM_LIST )
%lkbefore ( data = foo , vars = %foolist() )
%mods ( data = foo , vars = %foolist() , out = one)
%lkafter ( data = foo , vars = %foolist() )
%mend driver ;
%driver ()
Clearly it is too much work for the small example you gave, but
that work is not unreasonable on the scale that you claim your
example represents. The benefits in terms of readability,
debugging, and maintenance will pay big dividends for the small
amount of extra work.
Now you might argue that the programmers you work with can not
stand this level of macro usage. If true, I would argue that
they have no business working with 1000 line programs of open
SAS
code.
Or you might argue that you do not have the time to build
programs this way. I would turn it around and say I do not have
the time to write 1000 line programs that are not designed this
way.
Ian_Whitlock@comcast.net
================================
Date: Thu, 9 Dec 2004 22:15:34 -0600
Reply-To: "Dunn, Toby" <Toby.Dunn@TEA.STATE.TX.US>
Sender: "SAS(r) Discussion"
From: "Dunn, Toby" <Toby.Dunn@TEA.STATE.TX.US>
Subject: Re: How to define a global determistic variable
without using
Macro
Comments: To: ian.whitlock@comcast.net
Content-Type: text/plain; charset="iso-8859-1"
Ian,
Don't get me wrong I respect you and hope i am not beating this
gloabal macro drum for the sake of beating it. I really like
learning and refining my knowledge. Gez if you gave a two month
class on programming i think I would take a sabbatical and go
learn from the master.
As for an example:
Hmmm...All I have is programs over 1000 lines long, which is a
bit much to put in an email, talk about boring you to sleep (Ian
with a sense of humor, I like it). However, I will attempt to
contrive one up....lol
Say a list created on the fly that needs to be used in both var
statements in say several procs and also in a title.
I usually create these lists with proc sql using a into
statement. Then use that variable every where I need it.
example,
proc sql noprint;
select name into : var_list separated by " "
from dictionary.columns
where libname = "WORK" and memname = "FOO" and type =
"NUM";
%let stop = &sqlobs;
quit;
title "sample of variables &vae_list before treatment";
proc print
data = foo;
var &var_list;
run;
data one;
set foo;
array nums (&stop) &var_list;
data step code here....
run;
title "report of data after treatment for variables &var_list";
proc report;
code ....
run;
I know its simplistic but imagine a bunch of different procs
and/or data steps. I have had to created programs like this.
Now
if i were to put the proc sql statement inside of a %macro def
it
would be well silly in my opinion due, the macro facility would
having to run the sql statement 'N' number of times. Now let us
consider other programers coming after us to mantain this
program. For most programmers I have found they have a sever
phobia about %macro's, I liken it to same fear of using letters
in equations in a biggining algebra class. So I think depending
on the shop, perhaps it is better, in some not all instamces, to
use a global variable rather than all local variables all the
time.
You said, "Global variables cannot be global constants -
variable
values can be changed, constant values cannot be." I agree and
as you point out we can treat them as constant, but some
possible
badness namely with maintance programmers, could happen. Could
we
not write the code in such away that we atleast minimize the
risk
of changing them. If a global macro variable, is to be treated
as a constant then should we not set up some method to insure
that future programmers and us as the writer to not use it as a
value that we should change.. I wonder if a good naming scheme
and in program documentation could not do this for us.
You stated "
Only if it is wise to break the rule. All variables, macros,
etc. have undesireable side effects. The question is always -
do
the benefits out weigh the cost. "
Now this with your papers and posts on global macro vars and
macro design issues makes perfect sense. I think I remember
reading something along this line in Code Complete the other
night. It was the one line I really wanted to see in bothe
design issue papers and the write up you did for the DCSUG news
letter over using %global varibales.
Cumbersome and contrived in retrospect was perhaps a poor choice
of word on my part. I felt it was cumber some when a %let
statement would have used fewer keystrokes and required less
info. for the programmer to need to understand.
"I doubt if you are losing your mind. Clue about what?"
Clue about SAS and Macro programming of course.
Toby Dunn (aka. junior)
<snip history>
| 
