LIBNAME SYS_LIB BASE "/SASDATASET/DES/Data/Data_Migration/Source_Folder/system"; %include "/SASCODE/SDB/SAS_Lib_Details/sdb_templates.sas"; %include "/SASCODE/SDB/SAS_Lib_Details/sdb_dsa_format.sas"; proc sql noprint; select user_name into :uid from SYS_LIB.lib_detail where libname = 'DES_SDB' ; select password into :pwd from SYS_LIB.lib_detail where libname = 'DES_SDB' ; select PATH into :path from SYS_LIB.lib_detail where libname = 'DES_SDB' ; quit; libname DES_SDB oracle schema = &uid user = &uid password = &pwd path = &path; options mprint mlogic symbolgen missing = ''; options nodate nonumber; /*options orientation=landscape;*/ options papersize=("15in","18in"); data test; set DES_SDB.JOS_DIST_LOOP; if _n_>5 then stop; run; proc print data=test;run; %macro report; %let district = %sysfunc(strip(&district)); %let year = %sysfunc(strip(&year)); %let month = %sysfunc(strip(&month)); %let table = %sysfunc(strip(ADF_1_1_1)); %let REPORT_TYPE = %sysfunc(strip(ADF)); %let rpt_title = Important Parameters of Dispensaries in District; %if &month >=4 %then %do; %let fin_month = %eval(&month-3); %end; %else %do; %let fin_month = %eval(&month+9); %end; %put &fin_month; %if &month >=4 %then %do; %let REF_YEAR = &YEAR; %end; %else %do; %let REF_YEAR = %eval(&YEAR+1); %end; %put &REF_YEAR; data _null_; call symput ('REFERENCE_YEAR',cat(put(&YEAR,4.),'-',substr(put((&YEAR+1),4.),3,2))); run; %put &REFERENCE_YEAR; proc format; value NA . = 'N.A.' other = [10.0]; run; %global COUNT_DIST; %if "&district" = "All" %then %do; PROC SQL NOPRINT; SELECT count(id) INTO :COUNT_DIST FROM DES_SDB.JOS_DIST_LOOP where id ne 35; QUIT; %end; %else %do; PROC SQL NOPRINT; SELECT id INTO :COUNT_DIST FROM DES_SDB.JOS_DIST_LOOP where DISTRICT_NAME="&district"; QUIT; %end; %DO I = 1 %TO &COUNT_DIST; %if "&district" ne "All" %then %do; %let I = &COUNT_DIST; %end; proc sql noprint; select STRIP(District_Name) as District_Name into : districtname from DES_SDB.JOS_DIST_LOOP where id = &I; select Dist_Code as Dist_Code into : dist from DES_SDB.JOS_DIST_LOOP where id = &I; select STRIP(Month_name) as Month_name into : month_name from DES_SDB.MONTH_ENG where month_no=&month; quit; %put &dist; %let districtname = %sysfunc(strip(&districtname)); %let month_name = %sysfunc(strip(&month_name)); %symdel sc psc; proc sql; create table service_charge as select DIST_CODE, YEAR, MONTH, case when MONTH between 4 and 12 then (month-3) else (month+9) end as REF_MONTH, sum(XRAY_SONOGRAPHY) as XRAY_SONOGRAPHY, sum(POULTRY_TRAINING) as POULTRY_TRAINING, sum(OPD) as OPD, sum(CASTRATIONS) as CASTRATIONS, sum(OPERATIONS) as OPERATIONS, sum(AI_ALLIED_SERVICES) as AI_ALLIED_SERVICES, sum(PREVENTIVE_VACCINATIONS) as PREVENTIVE_VACCINATIONS, sum(ANIMAL_HEALTH_PM_CERT) as ANIMAL_HEALTH_PM_CERT, sum(LAB_EXAM) as LAB_EXAM, sum(ANIMAL_PRES_ACT) as ANIMAL_PRES_ACT, sum(EXAM_MEAT_MUT) as EXAM_MEAT_MUT, sum(EXAM_AH) as EXAM_AH, sum(OTHERS) as OTHERS from DES_SDB.JOS_MPR_SERVICE_CHARGES where DIST_CODE = &dist and MONTH = &month and YEAR = &year /*where DIST_CODE = 2 and MONTH = 5 and YEAR = 2014*/ group by DIST_CODE, MONTH, YEAR order by DIST_CODE, YEAR, MONTH ; quit; proc sql; create table service_charge1 as select DIST_CODE, YEAR, MONTH, sum(XRAY_SONOGRAPHY,POULTRY_TRAINING,OPD,CASTRATIONS,OPERATIONS,AI_ALLIED_SERVICES,PREVENTIVE_VACCINATIONS, ANIMAL_HEALTH_PM_CERT,LAB_EXAM,ANIMAL_PRES_ACT,EXAM_MEAT_MUT,EXAM_AH,OTHERS) as SCharge from service_charge ; quit; data abc; set service_charge1; call symput('schg',SCharge); run; %let sc = %sysfunc(strip(&schg)); data ax; set abc; sc1=input("&sc",30.); run; %put ≻ /*........................PROGRESSIVE SERVICE CHARGES........................................................*/ proc sql; create table prog_sc as select DIST_CODE, YEAR, MONTH, case when MONTH between 4 and 12 then (month-3) else (month+9) end as REF_MONTH, sum(XRAY_SONOGRAPHY) as XRAY_SONOGRAPHY, sum(POULTRY_TRAINING) as POULTRY_TRAINING, sum(OPD) as OPD, sum(CASTRATIONS) as CASTRATIONS, sum(OPERATIONS) as OPERATIONS, sum(AI_ALLIED_SERVICES) as AI_ALLIED_SERVICES, sum(PREVENTIVE_VACCINATIONS) as PREVENTIVE_VACCINATIONS, sum(ANIMAL_HEALTH_PM_CERT) as ANIMAL_HEALTH_PM_CERT, sum(LAB_EXAM) as LAB_EXAM, sum(ANIMAL_PRES_ACT) as ANIMAL_PRES_ACT, sum(EXAM_MEAT_MUT) as EXAM_MEAT_MUT, sum(EXAM_AH) as EXAM_AH, sum(OTHERS) as OTHERS from DES_SDB.JOS_MPR_SERVICE_CHARGES where DIST_CODE = &dist and calculated REF_MONTH <= &fin_month and YEAR = &year /*where DIST_CODE = 2 and MONTH <= 5 and YEAR = 2014*/ group by DIST_CODE, MONTH, YEAR order by DIST_CODE, YEAR, MONTH ; quit; proc sql; create table prog_sc_sum as select DIST_CODE, YEAR, sum(XRAY_SONOGRAPHY) as XRAY_SONOGRAPHY, sum(POULTRY_TRAINING) as POULTRY_TRAINING, sum(OPD) as OPD, sum(CASTRATIONS) as CASTRATIONS, sum(OPERATIONS) as OPERATIONS, sum(AI_ALLIED_SERVICES) as AI_ALLIED_SERVICES, sum(PREVENTIVE_VACCINATIONS) as PREVENTIVE_VACCINATIONS, sum(ANIMAL_HEALTH_PM_CERT) as ANIMAL_HEALTH_PM_CERT, sum(LAB_EXAM) as LAB_EXAM, sum(ANIMAL_PRES_ACT) as ANIMAL_PRES_ACT, sum(EXAM_MEAT_MUT) as EXAM_MEAT_MUT, sum(EXAM_AH) as EXAM_AH, sum(OTHERS) as OTHERS from prog_sc group by DIST_CODE, YEAR order by DIST_CODE, YEAR ; quit; proc sql; create table prog_sc_sum1 as select DIST_CODE, YEAR, sum(XRAY_SONOGRAPHY,POULTRY_TRAINING,OPD,CASTRATIONS,OPERATIONS,AI_ALLIED_SERVICES,PREVENTIVE_VACCINATIONS, ANIMAL_HEALTH_PM_CERT,LAB_EXAM,ANIMAL_PRES_ACT,EXAM_MEAT_MUT,EXAM_AH,OTHERS) as PSCharge from prog_sc_sum ; quit; data P_SC; set prog_sc_sum1; call symput('psch',PSCharge); run; %let psc = %sysfunc(strip(&psch)); data P_SC1; set P_SC; psc1=input("&psc",30.); run; %put &psc; %if &year <= 2011 %then %do; proc sql; create table trans as select REG_CODE, DIST_CODE, MONTH, YEAR, case when MONTH between 4 and 12 then YEAR else (YEAR+1) end as REF_YEAR, case when MONTH between 1 and 12 then cat(put(YEAR,4.),'-',substr(put((YEAR+1),4.),3,2)) end as REFERENCE_YEAR, sum(AI_F_INDIGENOUS ) as AI_F_INDIGENOUS label = '1(a) @AI Fresh-Indigenous', sum(AI_F_EXOTIC ) as AI_F_EXOTIC label = '1(a) @AI Fresh-Exotic', sum(AI_F_CROSSBRED ) as AI_F_CROSSBRED label = '1(a) @AI Fresh-Crossbred', sum(AI_F_BUFFALOE ) as AI_F_BUFFALOE label = '1(a) @AI Fresh-Buffaloe', sum(AI_R_INDIGENOUS ) as AI_R_INDIGENOUS label = '1(a) @AI (Repeat1+2)-Indigenous', sum(AI_R_EXOTIC ) as AI_R_EXOTIC label = '1(a) @AI (Repeat1+2)-Exotic', sum(AI_R_CROSSBRED ) as AI_R_CROSSBRED label = '1(a) @AI (Repeat1+2)-Crossbred', sum(AI_R_BUFFALOE ) as AI_R_BUFFALOE label = '1(a) @AI (Repeat1+2)-Buffaloe', sum(HQ_IND ) as HQ_IND label = '1(b) @AI HQ-Indigenous', sum(HQ_EXOTIC ) as HQ_EXOTIC label = '1(b) @AI HQ-Exotic', sum(HQ_CROSSBRED ) as HQ_CROSSBRED label = '1(b) @AI HQ-Crossbred', sum(HQ_BUFF ) as HQ_BUFF label = '1(b) @AI HQ-Buffaloe', sum(TOUR_IND ) as TOUR_IND label = '1(b) @AI on Tour-Indigenous', sum(TOUR_EXOTIC ) as TOUR_EXOTIC label = '1(b) @AI on Tour-Exotic', sum(TOUR_CROSSBRED ) as TOUR_CROSSBRED label = '1(b) @AI on Tour-Crossbred', sum(TOUR_BUFF ) as TOUR_BUFF label = '1(b) @AI on Tour-Buffaloe', sum(WC_IND ) as WC_IND label = '1(c) @AI Completed at Work Camp-Indigenous', sum(WC_EXOTIC ) as WC_EXOTIC label = '1(c) @AI Completed at Work Camp-Exotic', sum(WC_CROSSBRED ) as WC_CROSSBRED label = '1(c) @AI Completed at Work Camp-Crossbred', sum(WC_BUFF ) as WC_BUFF label = '1(c) @AI Completed at Work Camp-Buffaloe', sum(CB_INDIGENOUS ) as CB_INDIGENOUS label = '2 @Calves Born-Indigenous', sum(CB_CROSSBRED ) as CB_CROSSBRED label = '2 @Calves Born-Crossbred', sum(CB_BUFFALOE ) as CB_BUFFALOE label = '2 @Calves Born-Buffaloe', sum(PD_COWS ) as PD_COWS label = '3 @P.D.-Cows', sum(PD_BUFFALOES ) as PD_BUFFALOES label = '3 @P.D.-Buffaloe', sum(LN2_CONSUMED ) as LN2_CONSUMED label = '4 @LN2 Consumed (Ltrs.)- ', sum(INFERTILITY_COWS ) as INFERTILITY_COWS label = '5 @Infertility Tests-Cows', sum(INFERTILITY_BUFFALOES ) as INFERTILITY_BUFFALOES label = '5 @Infertility Tests-Buffaloe', sum(TREATMENT ) as TREATMENT label = '6 @Total Treatments- ', sum(CASTRATIONS ) as CASTRATIONS label = '7 @Total Castrations- ', sum(OPERATIONS_MAJOR ) as OPERATIONS_MAJOR label = '8 @Operations-Major', sum(OPERATIONS_MINOR ) as OPERATIONS_MINOR label = '8 @Operations-Minor', sum(VAC_HS ) as VAC_HS label = '9 @Vaccinations-HS', sum(VAC_BQ ) as VAC_BQ label = '9 @Vaccinations-BQ', sum(VAC_HSPLUSBQ ) as VAC_HSPLUSBQ label = '9 @Vaccinations-HS+BQ', sum(VAC_ETV_COW_BUFFALOE ) as VAC_ETV_COW_BUFFALOE label = '9 @Vaccinations-ETV(Cows and Buffalloes)', sum(VAC_FMD ) as VAC_FMD label = '9 @Vaccinations-FMD', sum(VAC_POX_COW_BUFFALOE ) as VAC_POX_COW_BUFFALOE label = '9 @Vaccinations-POX(Cows and Buffalloes)', sum(VAC_ETV_SHEEP_GOAT ) as VAC_ETV_SHEEP_GOAT label = '9 @Vaccinations-ETV (Sheep and Goat)', sum(VAC_POX_SHEEP_GOAT ) as VAC_POX_SHEEP_GOAT label = '9 @Vaccinations-Sheep Pox', sum(VAC_PPR ) as VAC_PPR label = '9 @Vaccinations-PPR', sum(VAC_ANTHRAX ) as VAC_ANTHRAX label = '9 @Vaccinations-Anthrax', sum(VAC_ANTIRABIES ) as VAC_ANTIRABIES label = '9 @Vaccinations-Anti Rabies', sum(VAC_FOWPOX ) as VAC_FOWPOX label = '9 @Vaccinations-Fowl Pox', sum(VAC_RANIKHET ) as VAC_RANIKHET label = '9 @Vaccinations-Ranikhet', sum(VAC_LASOTA ) as VAC_LASOTA label = '9 @Vaccinations-Lasota', sum(VAC_MAREKS ) as VAC_MAREKS label = '9 @Vaccinations-Mareks', sum(VAC_GAMBORO ) as VAC_GAMBORO label = '9 @Vaccinations-Gumboro', sum(VAC_OTHER ) as VAC_OTHER label = '9 @Vaccinations-Other', sum(SEED_DISTRIBUTION ) as SEED_DISTRIBUTION label = '10 @Seed Distribution in kg- ', sum(SAPPLINGS_DISTRIBUTION ) as SAPPLINGS_DISTRIBUTION label = '11 @Sapplings Distribution- ', sum(MINIKIT ) as MINIKIT label = '12 @Minikit- ', sum(WORKCAMP ) as WORKCAMP label = '13 @Workcamp- ', sum(CALF_RALLIES ) as CALF_RALLIES label = '14 @Calf Rallies- ', sum(SET_MICROSCOPE ) as SET_MICROSCOPE label = '15 @Sample Examination Tests-On Microscope', sum(SET_HAEMATOLOGICAL ) as SET_HAEMATOLOGICAL label = '15 @Sample Examination Tests-On Heamatological Analyser', sum(SET_BIOCHEMICAL ) as SET_BIOCHEMICAL label = '15 @Sample Examination Tests-On Biochemical Analyser', sum(EGG_HATCH_DISTRIBUTION ) as EGG_HATCH_DISTRIBUTION label = '16 @Hatching Eggs Distribution- ', sum(DAY_OLD_CHICKS ) as DAY_OLD_CHICKS label = '17 @Distribution of Day Old Chicks- ', sum(DIST_PULLETS ) as DIST_PULLETS label = '18 @Distribution of Pullets (10+1 Unit)- ', sum(DIST_MILCH_CATTLE ) as DIST_MILCH_CATTLE label = '19 @Distribution of Milch Animals Cattle (1+1 Unit)- ', sum(DIST_MILCH_BUFF ) as DIST_MILCH_BUFF label = '20 @Distribution of Milch Animals Buffaloes(1+1 Unit)- ', sum(DIST_SHEEP ) as DIST_SHEEP label = '21 @Distribution of Sheep (10+1 Unit)-', sum(DIST_GOAT ) as DIST_GOAT label = '22 @Distribution of Goat (10+1 Unit)-', sum(PM_NC_LARGEANIMALS ) as PM_NC_LARGEANIMALS label = '23 @Postmortem-Natural Calamities Larg Animals', sum(PM_NC_SMALLANIMALS ) as PM_NC_SMALLANIMALS label = '23 @Postmortem-Natural Calamities Small Animals', sum(PM_NC_BIRDS ) as PM_NC_BIRDS label = '23 @Postmortem-Natural Calamities Birds', sum(PM_VL_LARGEANIMALS ) as PM_VL_LARGEANIMALS label = '23 @Postmortem-Veterolegal Larg Animals', sum(PM_VL_SMALLANIMALS ) as PM_VL_SMALLANIMALS label = '23 @Postmortem-Veterolegal Small Animals', sum(PM_VL_BIRDS ) as PM_VL_BIRDS label = '23 @Postmortem-Veterolegal Birds', sum(PM_DG_LARGEANIMALS ) as PM_DG_LARGEANIMALS label = '23 @Postmortem-Diagnostic Larg Animals', sum(PM_DG_SMALLANIMALS ) as PM_DG_SMALLANIMALS label = '23 @Postmortem-Diagnostic Small Animals', sum(PM_DG_BIRDS ) as PM_DG_BIRDS label = '23 @Postmortem-Diagnostic Birds', sum(MILKCOMP_BLOCK ) as MILKCOMP_BLOCK label = '24 @Milk Competition-Block Level', sum(MILKCOMP_STATE ) as MILKCOMP_STATE label = '24 @Milk Competition-State Level', sum(MILKCOMP_NATIONAL ) as MILKCOMP_NATIONAL label = '24 @Milk Competition-National Level', sum(SONOGRAPHY ) as SONOGRAPHY label = '25 @Sonography- ', sum(XRAY ) as XRAY label = '26 @X Ray- ', sum(SERVICE_CHARGES ) as SERVICE_CHARGES label = '27(a)@Total Service Charges(in Rs.)- ', sum(SC_MLDB ) as SC_MLDB label = '27(b)@Service Charges For AIdeposited with MLDB(in Rs.)- ', sum(SE_WORKING ) as SE_WORKING label = '28 @Status of Equipments-Working', sum(SE_FAULTY ) as SE_FAULTY label = '28 @Status of Equipments-Faulty', sum(SE_DISPOSED ) as SE_DISPOSED label = '28 @Status of Equipments-To be disposed', sum(SE_SURPLUS ) as SE_SURPLUS label = '28 @Status of Equipments-Suplus', sum(BP_IND ) as BP_IND label = '29 @Indigenous-Breedable Population', sum(BP_EXOTIC ) as BP_EXOTIC label = '29 @Exotic-Breedable Population', sum(BP_CROSSBRED ) as BP_CROSSBRED label = '29 @Crossbred-Breedable Population', sum(BP_BUFF ) as BP_BUFF label = '29 @Buffaloe-Breedable Population' from DES_SDB.JOS_MPR_DISPENSARY where DIST_CODE = &dist and MONTH = &month and YEAR = &year group by REG_CODE, DIST_CODE, MONTH, YEAR order by REF_YEAR, MONTH ; quit; proc sql noprint; select * from trans; quit; %if &sqlobs = 0 %then %do; proc sql; insert into trans (DIST_CODE) values(&dist) ; quit; %end; PROC TRANSPOSE DATA=trans OUT=WORK.trans1 PREFIX=Column NAME=Source LABEL=Label; BY REG_CODE DIST_CODE MONTH YEAR REF_YEAR REFERENCE_YEAR ; VAR BP_IND BP_EXOTIC BP_CROSSBRED BP_BUFF AI_F_INDIGENOUS AI_F_EXOTIC AI_F_CROSSBRED AI_F_BUFFALOE AI_R_INDIGENOUS AI_R_EXOTIC AI_R_CROSSBRED AI_R_BUFFALOE HQ_IND HQ_EXOTIC HQ_CROSSBRED HQ_BUFF TOUR_IND TOUR_EXOTIC TOUR_CROSSBRED TOUR_BUFF WC_IND WC_EXOTIC WC_CROSSBRED WC_BUFF CB_INDIGENOUS CB_CROSSBRED CB_BUFFALOE PD_COWS PD_BUFFALOES LN2_CONSUMED INFERTILITY_COWS INFERTILITY_BUFFALOES TREATMENT CASTRATIONS OPERATIONS_MAJOR OPERATIONS_MINOR VAC_HS VAC_BQ VAC_HSPLUSBQ VAC_ETV_COW_BUFFALOE VAC_FMD VAC_POX_COW_BUFFALOE VAC_ETV_SHEEP_GOAT VAC_POX_SHEEP_GOAT VAC_PPR VAC_ANTHRAX VAC_ANTIRABIES VAC_FOWPOX VAC_RANIKHET VAC_LASOTA VAC_MAREKS VAC_GAMBORO VAC_OTHER SEED_DISTRIBUTION SAPPLINGS_DISTRIBUTION MINIKIT WORKCAMP CALF_RALLIES SET_MICROSCOPE SET_HAEMATOLOGICAL SET_BIOCHEMICAL EGG_HATCH_DISTRIBUTION DAY_OLD_CHICKS DIST_PULLETS DIST_MILCH_CATTLE DIST_MILCH_BUFF DIST_SHEEP DIST_GOAT PM_NC_LARGEANIMALS PM_NC_SMALLANIMALS PM_NC_BIRDS PM_VL_LARGEANIMALS PM_VL_SMALLANIMALS PM_VL_BIRDS PM_DG_LARGEANIMALS PM_DG_SMALLANIMALS PM_DG_BIRDS MILKCOMP_BLOCK MILKCOMP_STATE MILKCOMP_NATIONAL SONOGRAPHY XRAY SERVICE_CHARGES SC_MLDB SE_WORKING SE_FAULTY SE_DISPOSED SE_SURPLUS ; RUN; QUIT; data s1; set trans1; sr=_n_; run; %end; %else %do; proc sql; create table trans_sub_a as select a.REG_CODE, a.DIST_CODE, a.MONTH, a.YEAR, case when MONTH between 4 and 12 then YEAR else (YEAR+1) end as REF_YEAR, case when MONTH between 1 and 12 then cat(put(YEAR,4.),'-',substr(put((YEAR+1),4.),3,2)) end as REFERENCE_YEAR, sum(a.LN2_CONSUMED ) as LN2_CONSUMED label = '4 @LN2 Consumed (Ltrs.)- ', sum(a.INFERTILITY_COWS ) as INFERTILITY_COWS label = '5 @Infertility Tests-Cows', sum(a.INFERTILITY_BUFFALOES ) as INFERTILITY_BUFFALOES label = '5 @Infertility Tests-Buffaloe', sum(a.TREATMENT ) as TREATMENT label = '6 @Total Treatments- ', sum(a.OPERATIONS_MAJOR ) as OPERATIONS_MAJOR label = '8 @Operations-Major', sum(a.OPERATIONS_MINOR ) as OPERATIONS_MINOR label = '8 @Operations-Minor', sum(a.SEED_DISTRIBUTION ) as SEED_DISTRIBUTION label = '10 @Seed Distribution in kg- ', sum(a.SAPPLINGS_DISTRIBUTION ) as SAPPLINGS_DISTRIBUTION label = '11 @Sapplings Distribution- ', sum(a.MINIKIT ) as MINIKIT label = '12 @Minikit- ', sum(a.WORKCAMP ) as WORKCAMP label = '13 @Workcamp- ', sum(a.CALF_RALLIES ) as CALF_RALLIES label = '14 @Calf Rallies- ', sum(a.SET_MICROSCOPE ) as SET_MICROSCOPE label = '15 @Sample Examination Tests-On Microscope', sum(a.SET_HAEMATOLOGICAL ) as SET_HAEMATOLOGICAL label = '15 @Sample Examination Tests-On Heamatological Analyser', sum(a.SET_BIOCHEMICAL ) as SET_BIOCHEMICAL label = '15 @Sample Examination Tests-On Biochemical Analyser', sum(a.EGG_HATCH_DISTRIBUTION ) as EGG_HATCH_DISTRIBUTION label = '16 @Hatching Eggs Distribution- ', sum(a.DAY_OLD_CHICKS ) as DAY_OLD_CHICKS label = '17 @Distribution of Day Old Chicks- ', sum(a.DIST_PULLETS ) as DIST_PULLETS label = '18 @Distribution of Pullets (10+1 Unit)- ', sum(a.DIST_MILCH_CATTLE ) as DIST_MILCH_CATTLE label = '19 @Distribution of Milch Animals Cattle (1+1 Unit)- ', sum(a.DIST_MILCH_BUFF ) as DIST_MILCH_BUFF label = '20 @Distribution of Milch Animals Buffaloes(1+1 Unit)- ', sum(a.DIST_SHEEP ) as DIST_SHEEP label = '21 @Distribution of Sheep (10+1 Unit)-', sum(a.DIST_GOAT ) as DIST_GOAT label = '22 @Distribution of Goat (10+1 Unit)-', sum(a.PM_NC_LARGEANIMALS ) as PM_NC_LARGEANIMALS label = '23 @Postmortem-Natural Calamities Larg Animals', sum(a.PM_NC_SMALLANIMALS ) as PM_NC_SMALLANIMALS label = '23 @Postmortem-Natural Calamities Small Animals', sum(a.PM_NC_BIRDS ) as PM_NC_BIRDS label = '23 @Postmortem-Natural Calamities Birds', sum(a.PM_VL_LARGEANIMALS ) as PM_VL_LARGEANIMALS label = '23 @Postmortem-Veterolegal Larg Animals', sum(a.PM_VL_SMALLANIMALS ) as PM_VL_SMALLANIMALS label = '23 @Postmortem-Veterolegal Small Animals', sum(a.PM_VL_BIRDS ) as PM_VL_BIRDS label = '23 @Postmortem-Veterolegal Birds', sum(a.PM_DG_LARGEANIMALS ) as PM_DG_LARGEANIMALS label = '23 @Postmortem-Diagnostic Larg Animals', sum(a.PM_DG_SMALLANIMALS ) as PM_DG_SMALLANIMALS label = '23 @Postmortem-Diagnostic Small Animals', sum(a.PM_DG_BIRDS ) as PM_DG_BIRDS label = '23 @Postmortem-Diagnostic Birds', sum(a.MILKCOMP_BLOCK ) as MILKCOMP_BLOCK label = '24 @Milk Competition-Block Level', sum(a.MILKCOMP_STATE ) as MILKCOMP_STATE label = '24 @Milk Competition-State Level', sum(a.MILKCOMP_NATIONAL ) as MILKCOMP_NATIONAL label = '24 @Milk Competition-National Level', sum(a.SONOGRAPHY ) as SONOGRAPHY label = '25 @Sonography- ', sum(a.XRAY ) as XRAY label = '26 @X Ray- ', /*sum(a.SERVICE_CHARGES ) as SERVICE_CHARGES label = '27(a)@Total Service Charges(in Rs.)- ',*/ input("&sc",30.) as SERVICE_CHARGES label = '27(a)@Total Service Charges(in Rs.)- ', sum(a.SC_MLDB ) as SC_MLDB label = '27(b)@Service Charges For AIdeposited with MLDB(in Rs.)- ', sum(a.SE_WORKING ) as SE_WORKING label = '28 @Status of Equipments-Working', sum(a.SE_FAULTY ) as SE_FAULTY label = '28 @Status of Equipments-Faulty', sum(a.SE_DISPOSED ) as SE_DISPOSED label = '28 @Status of Equipments-To be disposed', sum(a.SE_SURPLUS ) as SE_SURPLUS label = '28 @Status of Equipments-Suplus', sum(a.BP_IND ) as BP_IND label = '29 @Indigenous-Breedable Population', sum(a.BP_EXOTIC ) as BP_EXOTIC label = '29 @Exotic-Breedable Population', sum(a.BP_CROSSBRED ) as BP_CROSSBRED label = '29 @Crossbred-Breedable Population', sum(a.BP_BUFF ) as BP_BUFF label = '29 @Buffaloe-Breedable Population' from DES_SDB.JOS_MPR_DISPENSARY a where a.DIST_CODE = &dist and a.MONTH = &month and a.YEAR = &year group by a.REG_CODE, a.DIST_CODE, a.MONTH, a.YEAR order by REF_YEAR, MONTH ; quit; proc sql noprint; select * from trans_sub_a; quit; %if &sqlobs = 0 %then %do; proc sql; insert into trans_sub_a (DIST_CODE, MONTH, YEAR) values(&dist,&month,&year) ; quit; %end; proc sql; create table trans_sub_b as select b.REG_CODE, b.DIST_CODE, b.MONTH, b.YEAR, case when MONTH between 4 and 12 then YEAR else (YEAR+1) end as REF_YEAR, case when MONTH between 1 and 12 then cat(put(YEAR,4.),'-',substr(put((YEAR+1),4.),3,2)) end as REFERENCE_YEAR, sum(b.AI_F_INDIGENOUS ) as AI_F_INDIGENOUS label = '1(a) @AI Fresh-Indigenous', sum(b.AI_F_EXOTIC ) as AI_F_EXOTIC label = '1(a) @AI Fresh-Exotic', sum(b.AI_F_CROSSBRED ) as AI_F_CROSSBRED label = '1(a) @AI Fresh-Crossbred', sum(b.AI_F_BUFFALOE ) as AI_F_BUFFALOE label = '1(a) @AI Fresh-Buffaloe', sum(b.AI_R_INDIGENOUS ) as AI_R_INDIGENOUS label = '1(a) @AI (Repeat1+2)-Indigenous', sum(b.AI_R_EXOTIC ) as AI_R_EXOTIC label = '1(a) @AI (Repeat1+2)-Exotic', sum(b.AI_R_CROSSBRED ) as AI_R_CROSSBRED label = '1(a) @AI (Repeat1+2)-Crossbred', sum(b.AI_R_BUFFALOE ) as AI_R_BUFFALOE label = '1(a) @AI (Repeat1+2)-Buffaloe', sum(b.HQ_IND ) as HQ_IND label = '1(b) @AI HQ-Indigenous', sum(b.HQ_EXOTIC ) as HQ_EXOTIC label = '1(b) @AI HQ-Exotic', sum(b.HQ_CROSSBRED ) as HQ_CROSSBRED label = '1(b) @AI HQ-Crossbred', sum(b.HQ_BUFF ) as HQ_BUFF label = '1(b) @AI HQ-Buffaloe', sum(b.TOUR_IND ) as TOUR_IND label = '1(b) @AI on Tour-Indigenous', sum(b.TOUR_EXOTIC ) as TOUR_EXOTIC label = '1(b) @AI on Tour-Exotic', sum(b.TOUR_CROSSBRED ) as TOUR_CROSSBRED label = '1(b) @AI on Tour-Crossbred', sum(b.TOUR_BUFF ) as TOUR_BUFF label = '1(b) @AI on Tour-Buffaloe', sum(b.WC_IND ) as WC_IND label = '1(c) @AI Completed at Work Camp-Indigenous', sum(b.WC_EXOTIC ) as WC_EXOTIC label = '1(c) @AI Completed at Work Camp-Exotic', sum(b.WC_CROSSBRED ) as WC_CROSSBRED label = '1(c) @AI Completed at Work Camp-Crossbred', sum(b.WC_BUFF ) as WC_BUFF label = '1(c) @AI Completed at Work Camp-Buffaloe', sum(b.CB_INDIGENOUS ) as CB_INDIGENOUS label = '2 @Calves Born-Indigenous', sum(b.CB_CROSSBRED ) as CB_CROSSBRED label = '2 @Calves Born-Crossbred', sum(b.CB_BUFFALOE ) as CB_BUFFALOE label = '2 @Calves Born-Buffaloe', sum(b.PD_COWS ) as PD_COWS label = '3 @P.D.-Cows', sum(b.PD_BUFFALOES ) as PD_BUFFALOES label = '3 @P.D.-Buffaloe', sum(b.CASTRATIONS ) as CASTRATIONS label = '7 @Total Castrations- ', sum(b.VAC_HS ) as VAC_HS label = '9 @Vaccinations-HS', sum(b.VAC_BQ ) as VAC_BQ label = '9 @Vaccinations-BQ', sum(b.VAC_HSPLUSBQ ) as VAC_HSPLUSBQ label = '9 @Vaccinations-HS+BQ', sum(b.VAC_ETV_COW_BUFFALOE ) as VAC_ETV_COW_BUFFALOE label = '9 @Vaccinations-ETV(Cows and Buffalloes)', sum(b.VAC_FMD ) as VAC_FMD label = '9 @Vaccinations-FMD', sum(b.VAC_POX_COW_BUFFALOE ) as VAC_POX_COW_BUFFALOE label = '9 @Vaccinations-POX(Cows and Buffalloes)', sum(b.VAC_ETV_SHEEP_GOAT ) as VAC_ETV_SHEEP_GOAT label = '9 @Vaccinations-ETV (Sheep and Goat)', sum(b.VAC_POX_SHEEP_GOAT ) as VAC_POX_SHEEP_GOAT label = '9 @Vaccinations-Sheep Pox', sum(b.VAC_PPR ) as VAC_PPR label = '9 @Vaccinations-PPR', sum(b.VAC_ANTHRAX ) as VAC_ANTHRAX label = '9 @Vaccinations-Anthrax', sum(b.VAC_ANTIRABIES ) as VAC_ANTIRABIES label = '9 @Vaccinations-Anti Rabies', sum(b.VAC_FOWPOX ) as VAC_FOWPOX label = '9 @Vaccinations-Fowl Pox', sum(b.VAC_RANIKHET ) as VAC_RANIKHET label = '9 @Vaccinations-Ranikhet', sum(b.VAC_LASOTA ) as VAC_LASOTA label = '9 @Vaccinations-Lasota', sum(b.VAC_MAREKS ) as VAC_MAREKS label = '9 @Vaccinations-Mareks', sum(b.VAC_GAMBORO ) as VAC_GAMBORO label = '9 @Vaccinations-Gumboro', sum(b.VAC_OTHER ) as VAC_OTHER label = '9 @Vaccinations-Other' from DES_SDB.JOS_MPR_DISPENSARY_SUBORDINATE b where b.DIST_CODE = &dist and b.MONTH = &month and b.YEAR = &year group by b.REG_CODE, b.DIST_CODE, b.MONTH, b.YEAR order by REF_YEAR, MONTH ; quit; proc sql noprint; select * from trans_sub_b; quit; %if &sqlobs = 0 %then %do; proc sql; insert into trans_sub_b (DIST_CODE, MONTH, YEAR) values(&dist,&month,&year) ; quit; %end; data ab_join; marge trans_sub_a trans_sub_b; run; proc sql noprint; select * from ab_join; quit; %if &sqlobs = 0 %then %do; proc sql; insert into ab_join (DIST_CODE, MONTH, YEAR) values(&dist,&month,&year) ; quit; %end; PROC TRANSPOSE DATA=ab_join OUT=WORK.ab_join_trans PREFIX=Column NAME=Source LABEL=Label; BY REG_CODE DIST_CODE MONTH YEAR REF_YEAR REFERENCE_YEAR ; VAR BP_IND BP_EXOTIC BP_CROSSBRED BP_BUFF AI_F_INDIGENOUS AI_F_EXOTIC AI_F_CROSSBRED AI_F_BUFFALOE AI_R_INDIGENOUS AI_R_EXOTIC AI_R_CROSSBRED AI_R_BUFFALOE HQ_IND HQ_EXOTIC HQ_CROSSBRED HQ_BUFF TOUR_IND TOUR_EXOTIC TOUR_CROSSBRED TOUR_BUFF WC_IND WC_EXOTIC WC_CROSSBRED WC_BUFF CB_INDIGENOUS CB_CROSSBRED CB_BUFFALOE PD_COWS PD_BUFFALOES LN2_CONSUMED INFERTILITY_COWS INFERTILITY_BUFFALOES TREATMENT CASTRATIONS OPERATIONS_MAJOR OPERATIONS_MINOR VAC_HS VAC_BQ VAC_HSPLUSBQ VAC_ETV_COW_BUFFALOE VAC_FMD VAC_POX_COW_BUFFALOE VAC_ETV_SHEEP_GOAT VAC_POX_SHEEP_GOAT VAC_PPR VAC_ANTHRAX VAC_ANTIRABIES VAC_FOWPOX VAC_RANIKHET VAC_LASOTA VAC_MAREKS VAC_GAMBORO VAC_OTHER SEED_DISTRIBUTION SAPPLINGS_DISTRIBUTION MINIKIT WORKCAMP CALF_RALLIES SET_MICROSCOPE SET_HAEMATOLOGICAL SET_BIOCHEMICAL EGG_HATCH_DISTRIBUTION DAY_OLD_CHICKS DIST_PULLETS DIST_MILCH_CATTLE DIST_MILCH_BUFF DIST_SHEEP DIST_GOAT PM_NC_LARGEANIMALS PM_NC_SMALLANIMALS PM_NC_BIRDS PM_VL_LARGEANIMALS PM_VL_SMALLANIMALS PM_VL_BIRDS PM_DG_LARGEANIMALS PM_DG_SMALLANIMALS PM_DG_BIRDS MILKCOMP_BLOCK MILKCOMP_STATE MILKCOMP_NATIONAL SONOGRAPHY XRAY SERVICE_CHARGES SC_MLDB SE_WORKING SE_FAULTY SE_DISPOSED SE_SURPLUS ; RUN; QUIT; data s1; set ab_join_trans; sr=_n_; run; %end; /*...............................TARGET DATA..........................................*/ proc sql; create table trans_tar as select distinct REG_CODE, DIST_CODE, YEAR, &year as REF_YEAR, /*case when MONTH between 4 and 12 then YEAR else (YEAR+1) end as REF_YEAR,*/ case when MONTH between 1 and 12 then cat(put(YEAR,4.),'-',substr(put((YEAR+1),4.),3,2)) end as REFERENCE_YEAR, sum(AI_F_INDIGENOUS_tar ) as AI_F_INDIGENOUS label = '1(a) @AI Fresh-Indigenous', sum(AI_F_EXOTIC_tar ) as AI_F_EXOTIC label = '1(a) @AI Fresh-Exotic', sum(AI_F_CROSSBRED_tar ) as AI_F_CROSSBRED label = '1(a) @AI Fresh-Crossbred', sum(AI_F_BUFFALOE_tar ) as AI_F_BUFFALOE label = '1(a) @AI Fresh-Buffaloe', sum(AI_R_INDIGENOUS_tar ) as AI_R_INDIGENOUS label = '1(a) @AI (Repeat1+2)-Indigenous', sum(AI_R_EXOTIC_tar ) as AI_R_EXOTIC label = '1(a) @AI (Repeat1+2)-Exotic', sum(AI_R_CROSSBRED_tar ) as AI_R_CROSSBRED label = '1(a) @AI (Repeat1+2)-Crossbred', sum(AI_R_BUFFALOE_tar ) as AI_R_BUFFALOE label = '1(a) @AI (Repeat1+2)-Buffaloe', sum(HQ_IND_tar ) as HQ_IND label = '1(b) @AI HQ-Indigenous', sum(HQ_EXOTIC_tar ) as HQ_EXOTIC label = '1(b) @AI HQ-Exotic', sum(HQ_CROSSBRED_tar ) as HQ_CROSSBRED label = '1(b) @AI HQ-Crossbred', sum(HQ_BUFF_tar ) as HQ_BUFF label = '1(b) @AI HQ-Buffaloe', sum(TOUR_IND_tar ) as TOUR_IND label = '1(b) @AI on Tour-Indigenous', sum(TOUR_EXOTIC_tar ) as TOUR_EXOTIC label = '1(b) @AI on Tour-Exotic', sum(TOUR_CROSSBRED_tar ) as TOUR_CROSSBRED label = '1(b) @AI on Tour-Crossbred', sum(TOUR_BUFF_tar ) as TOUR_BUFF label = '1(b) @AI on Tour-Buffaloe', sum(WC_IND_tar ) as WC_IND label = '1(c) @AI Completed at Work Camp-Indigenous', sum(WC_EXOTIC_tar ) as WC_EXOTIC label = '1(c) @AI Completed at Work Camp-Exotic', sum(WC_CROSSBRED_tar ) as WC_CROSSBRED label = '1(c) @AI Completed at Work Camp-Crossbred', sum(WC_BUFF_tar ) as WC_BUFF label = '1(c) @AI Completed at Work Camp-Buffaloe', sum(CB_INDIGENOUS_tar ) as CB_INDIGENOUS label = '2 @Calves Born-Indigenous', sum(CB_CROSSBRED_tar ) as CB_CROSSBRED label = '2 @Calves Born-Crossbred', sum(CB_BUFFALOE_tar ) as CB_BUFFALOE label = '2 @Calves Born-Buffaloe', sum(PD_COWS_tar ) as PD_COWS label = '3 @P.D.-Cows', sum(PD_BUFFALOES_tar ) as PD_BUFFALOES label = '3 @P.D.-Buffaloe', sum(LN2_CONSUMED_tar ) as LN2_CONSUMED label = '4 @LN2 Consumed (Ltrs.)- ', sum(INFERTILITY_COWS_tar ) as INFERTILITY_COWS label = '5 @Infertility Tests-Cows', sum(INFERTILITY_BUFFALOES_tar ) as INFERTILITY_BUFFALOES label = '5 @Infertility Tests-Buffaloe', sum(TREATMENT_tar ) as TREATMENT label = '6 @Total Treatments- ', sum(CASTRATIONS_tar ) as CASTRATIONS label = '7 @Total Castrations- ', sum(OPERATIONS_MAJOR_tar ) as OPERATIONS_MAJOR label = '8 @Operations-Major', sum(OPERATIONS_MINOR_tar ) as OPERATIONS_MINOR label = '8 @Operations-Minor', sum(VAC_HS_tar ) as VAC_HS label = '9 @Vaccinations-HS', sum(VAC_BQ_tar ) as VAC_BQ label = '9 @Vaccinations-BQ', sum(VAC_HSBQ_tar ) as VAC_HSPLUSBQ label = '9 @Vaccinations-HS+BQ', sum(VAC_ETV_COW_BUFFALOE_tar ) as VAC_ETV_COW_BUFFALOE label = '9 @Vaccinations-ETV(Cows and Buffalloes)', sum(VAC_FMD_tar ) as VAC_FMD label = '9 @Vaccinations-FMD', sum(VAC_POX_COW_BUFFALOE_tar ) as VAC_POX_COW_BUFFALOE label = '9 @Vaccinations-POX(Cows and Buffalloes)', sum(VAC_ETV_SHEEP_GOAT_tar ) as VAC_ETV_SHEEP_GOAT label = '9 @Vaccinations-ETV (Sheep and Goat)', sum(VAC_POX_SHEEP_GOAT_tar ) as VAC_POX_SHEEP_GOAT label = '9 @Vaccinations-Sheep Pox', sum(VAC_PPR_tar ) as VAC_PPR label = '9 @Vaccinations-PPR', sum(VAC_ANTHRAX_tar ) as VAC_ANTHRAX label = '9 @Vaccinations-Anthrax', sum(VAC_ANTIRABIES_tar ) as VAC_ANTIRABIES label = '9 @Vaccinations-Anti Rabies', sum(VAC_FOWPOX_tar ) as VAC_FOWPOX label = '9 @Vaccinations-Fowl Pox', sum(VAC_RANIKHET_tar ) as VAC_RANIKHET label = '9 @Vaccinations-Ranikhet', sum(VAC_LASOTA_tar ) as VAC_LASOTA label = '9 @Vaccinations-Lasota', sum(VAC_MAREKS_tar ) as VAC_MAREKS label = '9 @Vaccinations-Mareks', sum(VAC_GAMBORO_tar ) as VAC_GAMBORO label = '9 @Vaccinations-Gumboro', sum(VAC_OTHER_tar ) as VAC_OTHER label = '9 @Vaccinations-Other', sum(SEED_DISTRIBUTION_tar ) as SEED_DISTRIBUTION label = '10 @Seed Distribution in kg- ', sum(SAPPLINGS_DISTRIBUTION_tar ) as SAPPLINGS_DISTRIBUTION label = '11 @Sapplings Distribution- ', sum(MINIKIT_tar ) as MINIKIT label = '12 @Minikit- ', sum(WORKCAMP_tar ) as WORKCAMP label = '13 @Workcamp- ', sum(CALF_RALLIES_tar ) as CALF_RALLIES label = '14 @Calf Rallies- ', sum(SET_MICROSCOPE_tar ) as SET_MICROSCOPE label = '15 @Sample Examination Tests-On Microscope', sum(SET_HAEMATOLOGICAL_tar ) as SET_HAEMATOLOGICAL label = '15 @Sample Examination Tests-On Heamatological Analyser', sum(SET_BIOCHEMICAL_tar ) as SET_BIOCHEMICAL label = '15 @Sample Examination Tests-On Biochemical Analyser', sum(EGG_HATCH_DISTRIBUTION_tar ) as EGG_HATCH_DISTRIBUTION label = '16 @Hatching Eggs Distribution- ', sum(DAY_OLD_CHICKS_tar ) as DAY_OLD_CHICKS label = '17 @Distribution of Day Old Chicks- ', sum(DIST_PULLETS_tar ) as DIST_PULLETS label = '18 @Distribution of Pullets (10+1 Unit)- ', sum(DIST_MILCH_CATTLE_tar ) as DIST_MILCH_CATTLE label = '19 @Distribution of Milch Animals Cattle (1+1 Unit)- ', sum(DIST_MILCH_BUFF_tar ) as DIST_MILCH_BUFF label = '20 @Distribution of Milch Animals Buffaloes (1+1 Unit)- ', sum(DIST_SHEEP_tar ) as DIST_SHEEP label = '21 @Distribution of Sheep (10+1 Unit)-', sum(DIST_GOAT_tar ) as DIST_GOAT label = '22 @Distribution of Goat (10+1 Unit)-', sum(PM_NC_LARGEANIMALS_tar ) as PM_NC_LARGEANIMALS label = '23 @Postmortem-Natural Calamities Larg Animals', sum(PM_NC_SMALLANIMALS_tar ) as PM_NC_SMALLANIMALS label = '23 @Postmortem-Natural Calamities Small Animals', sum(PM_NC_BIRDS_tar ) as PM_NC_BIRDS label = '23 @Postmortem-Natural Calamities Birds', sum(PM_VL_LARGEANIMALS_tar ) as PM_VL_LARGEANIMALS label = '23 @Postmortem-Veterolegal Larg Animals', sum(PM_VL_SMALLANIMALS_tar ) as PM_VL_SMALLANIMALS label = '23 @Postmortem-Veterolegal Small Animals', sum(PM_VL_BIRDS_tar ) as PM_VL_BIRDS label = '23 @Postmortem-Veterolegal Birds', sum(PM_DG_LARGEANIMALS_tar ) as PM_DG_LARGEANIMALS label = '23 @Postmortem-Diagnostic Larg Animals', sum(PM_DG_SMALLANIMALS_tar ) as PM_DG_SMALLANIMALS label = '23 @Postmortem-Diagnostic Small Animals', sum(PM_DG_BIRDS_tar ) as PM_DG_BIRDS label = '23 @Postmortem-Diagnostic Birds', sum(MILKCOMP_BLOCK_tar ) as MILKCOMP_BLOCK label = '24 @Milk Competition-Block Level', sum(MILKCOMP_STATE_tar ) as MILKCOMP_STATE label = '24 @Milk Competition-State Level', sum(MILKCOMP_NATIONAL_tar ) as MILKCOMP_NATIONAL label = '24 @Milk Competition-National Level', sum(SONOGRAPHY_tar ) as SONOGRAPHY label = '25 @Sonography- ', sum(XRAY_tar ) as XRAY label = '26 @X Ray- ', sum(SERVICE_CHARGES_tar ) as SERVICE_CHARGES label = '27(a)@Total Service Charges(in Rs.)- ', sum(SC_MLDB_tar ) as SC_MLDB label = '27(b)@Service Charges For AI deposited with MLDB(in Rs.)- ', sum(SE_WORKING_tar ) as SE_WORKING label = '28 @Status of Equipments-Working', sum(SE_FAULTY_tar ) as SE_FAULTY label = '28 @Status of Equipments-Faulty', sum(SE_DISPOSED_tar ) as SE_DISPOSED label = '28 @Status of Equipments-To be disposed', sum(SE_SURPLUS_tar ) as SE_SURPLUS label = '28 @Status of Equipments-Suplus' from DES_SDB.JOS_MPR_DISPENSARY_TARGET where DIST_CODE = &dist and YEAR = &year group by REG_CODE, DIST_CODE, YEAR ; quit; proc sql noprint; select * from trans_tar; quit; %if &sqlobs = 0 %then %do; proc sql; insert into trans_tar (DIST_CODE) values(&dist) ; quit; %end; PROC TRANSPOSE DATA=trans_tar OUT=WORK.trans1_tar PREFIX=Column NAME=Source LABEL=Label; BY REG_CODE DIST_CODE YEAR REF_YEAR REFERENCE_YEAR; VAR AI_F_INDIGENOUS AI_F_EXOTIC AI_F_CROSSBRED AI_F_BUFFALOE AI_R_INDIGENOUS AI_R_EXOTIC AI_R_CROSSBRED AI_R_BUFFALOE HQ_IND HQ_EXOTIC HQ_CROSSBRED HQ_BUFF TOUR_IND TOUR_EXOTIC TOUR_CROSSBRED TOUR_BUFF WC_IND WC_EXOTIC WC_CROSSBRED WC_BUFF CB_INDIGENOUS CB_CROSSBRED CB_BUFFALOE PD_COWS PD_BUFFALOES LN2_CONSUMED INFERTILITY_COWS INFERTILITY_BUFFALOES TREATMENT CASTRATIONS OPERATIONS_MAJOR OPERATIONS_MINOR VAC_HS VAC_BQ VAC_HSPLUSBQ VAC_ETV_COW_BUFFALOE VAC_FMD VAC_POX_COW_BUFFALOE VAC_ETV_SHEEP_GOAT VAC_POX_SHEEP_GOAT VAC_PPR VAC_ANTHRAX VAC_ANTIRABIES VAC_FOWPOX VAC_RANIKHET VAC_LASOTA VAC_MAREKS VAC_GAMBORO VAC_OTHER SEED_DISTRIBUTION SAPPLINGS_DISTRIBUTION MINIKIT WORKCAMP CALF_RALLIES SET_MICROSCOPE SET_HAEMATOLOGICAL SET_BIOCHEMICAL EGG_HATCH_DISTRIBUTION DAY_OLD_CHICKS DIST_PULLETS DIST_MILCH_CATTLE DIST_MILCH_BUFF DIST_SHEEP DIST_GOAT PM_NC_LARGEANIMALS PM_NC_SMALLANIMALS PM_NC_BIRDS PM_VL_LARGEANIMALS PM_VL_SMALLANIMALS PM_VL_BIRDS PM_DG_LARGEANIMALS PM_DG_SMALLANIMALS PM_DG_BIRDS MILKCOMP_BLOCK MILKCOMP_STATE MILKCOMP_NATIONAL SONOGRAPHY XRAY SERVICE_CHARGES SC_MLDB SE_WORKING SE_FAULTY SE_DISPOSED SE_SURPLUS ; RUN; QUIT; proc sql; create table trans_sub_a_prog as select a.REG_CODE, a.DIST_CODE, a.MONTH, case when MONTH between 4 and 12 then (month-3) else (month+9) end as REF_MONTH, a.YEAR, case when MONTH between 4 and 12 then YEAR else (YEAR+1) end as REF_YEAR, case when MONTH between 1 and 12 then cat(put(YEAR,4.),'-',substr(put((YEAR+1),4.),3,2)) end as REFERENCE_YEAR, sum(a.LN2_CONSUMED ) as LN2_CONSUMED label = '4 @LN2 Consumed (Ltrs.)- ', sum(a.INFERTILITY_COWS ) as INFERTILITY_COWS label = '5 @Infertility Tests-Cows', sum(a.INFERTILITY_BUFFALOES ) as INFERTILITY_BUFFALOES label = '5 @Infertility Tests-Buffaloe', sum(a.TREATMENT ) as TREATMENT label = '6 @Total Treatments- ', sum(a.OPERATIONS_MAJOR ) as OPERATIONS_MAJOR label = '8 @Operations-Major', sum(a.OPERATIONS_MINOR ) as OPERATIONS_MINOR label = '8 @Operations-Minor', sum(a.SEED_DISTRIBUTION ) as SEED_DISTRIBUTION label = '10 @Seed Distribution in kg- ', sum(a.SAPPLINGS_DISTRIBUTION ) as SAPPLINGS_DISTRIBUTION label = '11 @Sapplings Distribution- ', sum(a.MINIKIT ) as MINIKIT label = '12 @Minikit- ', sum(a.WORKCAMP ) as WORKCAMP label = '13 @Workcamp- ', sum(a.CALF_RALLIES ) as CALF_RALLIES label = '14 @Calf Rallies- ', sum(a.SET_MICROSCOPE ) as SET_MICROSCOPE label = '15 @Sample Examination Tests-On Microscope', sum(a.SET_HAEMATOLOGICAL ) as SET_HAEMATOLOGICAL label = '15 @Sample Examination Tests-On Heamatological Analyser', sum(a.SET_BIOCHEMICAL ) as SET_BIOCHEMICAL label = '15 @Sample Examination Tests-On Biochemical Analyser', sum(a.EGG_HATCH_DISTRIBUTION ) as EGG_HATCH_DISTRIBUTION label = '16 @Hatching Eggs Distribution- ', sum(a.DAY_OLD_CHICKS ) as DAY_OLD_CHICKS label = '17 @Distribution of Day Old Chicks- ', sum(a.DIST_PULLETS ) as DIST_PULLETS label = '18 @Distribution of Pullets (10+1 Unit)- ', sum(a.DIST_MILCH_CATTLE ) as DIST_MILCH_CATTLE label = '19 @Distribution of Milch Animals Cattle (1+1 Unit)- ', sum(a.DIST_MILCH_BUFF ) as DIST_MILCH_BUFF label = '20 @Distribution of Milch Animals Buffaloes(1+1 Unit)- ', sum(a.DIST_SHEEP ) as DIST_SHEEP label = '21 @Distribution of Sheep (10+1 Unit)-', sum(a.DIST_GOAT ) as DIST_GOAT label = '22 @Distribution of Goat (10+1 Unit)-', sum(a.PM_NC_LARGEANIMALS ) as PM_NC_LARGEANIMALS label = '23 @Postmortem-Natural Calamities Larg Animals', sum(a.PM_NC_SMALLANIMALS ) as PM_NC_SMALLANIMALS label = '23 @Postmortem-Natural Calamities Small Animals', sum(a.PM_NC_BIRDS ) as PM_NC_BIRDS label = '23 @Postmortem-Natural Calamities Birds', sum(a.PM_VL_LARGEANIMALS ) as PM_VL_LARGEANIMALS label = '23 @Postmortem-Veterolegal Larg Animals', sum(a.PM_VL_SMALLANIMALS ) as PM_VL_SMALLANIMALS label = '23 @Postmortem-Veterolegal Small Animals', sum(a.PM_VL_BIRDS ) as PM_VL_BIRDS label = '23 @Postmortem-Veterolegal Birds', sum(a.PM_DG_LARGEANIMALS ) as PM_DG_LARGEANIMALS label = '23 @Postmortem-Diagnostic Larg Animals', sum(a.PM_DG_SMALLANIMALS ) as PM_DG_SMALLANIMALS label = '23 @Postmortem-Diagnostic Small Animals', sum(a.PM_DG_BIRDS ) as PM_DG_BIRDS label = '23 @Postmortem-Diagnostic Birds', sum(a.MILKCOMP_BLOCK ) as MILKCOMP_BLOCK label = '24 @Milk Competition-Block Level', sum(a.MILKCOMP_STATE ) as MILKCOMP_STATE label = '24 @Milk Competition-State Level', sum(a.MILKCOMP_NATIONAL ) as MILKCOMP_NATIONAL label = '24 @Milk Competition-National Level', sum(a.SONOGRAPHY ) as SONOGRAPHY label = '25 @Sonography- ', sum(a.XRAY ) as XRAY label = '26 @X Ray- ', sum(a.SERVICE_CHARGES ) as SERVICE_CHARGES label = '27(a)@Total Service Charges(in Rs.)- ', sum(a.SC_MLDB ) as SC_MLDB label = '27(b)@Service Charges For AIdeposited with MLDB(in Rs.)- ', sum(a.SE_WORKING ) as SE_WORKING label = '28 @Status of Equipments-Working', sum(a.SE_FAULTY ) as SE_FAULTY label = '28 @Status of Equipments-Faulty', sum(a.SE_DISPOSED ) as SE_DISPOSED label = '28 @Status of Equipments-To be disposed', sum(a.SE_SURPLUS ) as SE_SURPLUS label = '28 @Status of Equipments-Suplus', sum(a.BP_IND ) as BP_IND label = '29 @Indigenous-Breedable Population', sum(a.BP_EXOTIC ) as BP_EXOTIC label = '29 @Exotic-Breedable Population', sum(a.BP_CROSSBRED ) as BP_CROSSBRED label = '29 @Crossbred-Breedable Population', sum(a.BP_BUFF ) as BP_BUFF label = '29 @Buffaloe-Breedable Population' from DES_SDB.JOS_MPR_DISPENSARY a where a.DIST_CODE = &dist and calculated REF_MONTH <= &fin_month and a.YEAR = &year /*where a.DIST_CODE = 1 and a.MONTH = 8 and a.YEAR = 2013*/ group by a.REG_CODE, a.DIST_CODE, a.MONTH, a.YEAR order by REF_YEAR, MONTH ; quit; proc sql; create table trans_sub_a_prog_sum as select distinct a.REG_CODE, a.DIST_CODE, &month as Month, a.YEAR, a.REFERENCE_YEAR, sum(a.LN2_CONSUMED ) as LN2_CONSUMED label = '4 @LN2 Consumed (Ltrs.)- ', sum(a.INFERTILITY_COWS ) as INFERTILITY_COWS label = '5 @Infertility Tests-Cows', sum(a.INFERTILITY_BUFFALOES ) as INFERTILITY_BUFFALOES label = '5 @Infertility Tests-Buffaloe', sum(a.TREATMENT ) as TREATMENT label = '6 @Total Treatments- ', sum(a.OPERATIONS_MAJOR ) as OPERATIONS_MAJOR label = '8 @Operations-Major', sum(a.OPERATIONS_MINOR ) as OPERATIONS_MINOR label = '8 @Operations-Minor', sum(a.SEED_DISTRIBUTION ) as SEED_DISTRIBUTION label = '10 @Seed Distribution in kg- ', sum(a.SAPPLINGS_DISTRIBUTION ) as SAPPLINGS_DISTRIBUTION label = '11 @Sapplings Distribution- ', sum(a.MINIKIT ) as MINIKIT label = '12 @Minikit- ', sum(a.WORKCAMP ) as WORKCAMP label = '13 @Workcamp- ', sum(a.CALF_RALLIES ) as CALF_RALLIES label = '14 @Calf Rallies- ', sum(a.SET_MICROSCOPE ) as SET_MICROSCOPE label = '15 @Sample Examination Tests-On Microscope', sum(a.SET_HAEMATOLOGICAL ) as SET_HAEMATOLOGICAL label = '15 @Sample Examination Tests-On Heamatological Analyser', sum(a.SET_BIOCHEMICAL ) as SET_BIOCHEMICAL label = '15 @Sample Examination Tests-On Biochemical Analyser', sum(a.EGG_HATCH_DISTRIBUTION ) as EGG_HATCH_DISTRIBUTION label = '16 @Hatching Eggs Distribution- ', sum(a.DAY_OLD_CHICKS ) as DAY_OLD_CHICKS label = '17 @Distribution of Day Old Chicks- ', sum(a.DIST_PULLETS ) as DIST_PULLETS label = '18 @Distribution of Pullets (10+1 Unit)- ', sum(a.DIST_MILCH_CATTLE ) as DIST_MILCH_CATTLE label = '19 @Distribution of Milch Animals Cattle (1+1 Unit)- ', sum(a.DIST_MILCH_BUFF ) as DIST_MILCH_BUFF label = '20 @Distribution of Milch Animals Buffaloes(1+1 Unit)- ', sum(a.DIST_SHEEP ) as DIST_SHEEP label = '21 @Distribution of Sheep (10+1 Unit)-', sum(a.DIST_GOAT ) as DIST_GOAT label = '22 @Distribution of Goat (10+1 Unit)-', sum(a.PM_NC_LARGEANIMALS ) as PM_NC_LARGEANIMALS label = '23 @Postmortem-Natural Calamities Larg Animals', sum(a.PM_NC_SMALLANIMALS ) as PM_NC_SMALLANIMALS label = '23 @Postmortem-Natural Calamities Small Animals', sum(a.PM_NC_BIRDS ) as PM_NC_BIRDS label = '23 @Postmortem-Natural Calamities Birds', sum(a.PM_VL_LARGEANIMALS ) as PM_VL_LARGEANIMALS label = '23 @Postmortem-Veterolegal Larg Animals', sum(a.PM_VL_SMALLANIMALS ) as PM_VL_SMALLANIMALS label = '23 @Postmortem-Veterolegal Small Animals', sum(a.PM_VL_BIRDS ) as PM_VL_BIRDS label = '23 @Postmortem-Veterolegal Birds', sum(a.PM_DG_LARGEANIMALS ) as PM_DG_LARGEANIMALS label = '23 @Postmortem-Diagnostic Larg Animals', sum(a.PM_DG_SMALLANIMALS ) as PM_DG_SMALLANIMALS label = '23 @Postmortem-Diagnostic Small Animals', sum(a.PM_DG_BIRDS ) as PM_DG_BIRDS label = '23 @Postmortem-Diagnostic Birds', sum(a.MILKCOMP_BLOCK ) as MILKCOMP_BLOCK label = '24 @Milk Competition-Block Level', sum(a.MILKCOMP_STATE ) as MILKCOMP_STATE label = '24 @Milk Competition-State Level', sum(a.MILKCOMP_NATIONAL ) as MILKCOMP_NATIONAL label = '24 @Milk Competition-National Level', sum(a.SONOGRAPHY ) as SONOGRAPHY label = '25 @Sonography- ', sum(a.XRAY ) as XRAY label = '26 @X Ray- ', /*sum(a.SERVICE_CHARGES ) as SERVICE_CHARGES label = '27(a)@Total Service Charges(in Rs.)- ',*/ input("&psc",30.) as SERVICE_CHARGES label = '27(a)@Total Service Charges(in Rs.)- ', sum(a.SC_MLDB ) as SC_MLDB label = '27(b)@Service Charges For AIdeposited with MLDB(in Rs.)- ', sum(a.SE_WORKING ) as SE_WORKING label = '28 @Status of Equipments-Working', sum(a.SE_FAULTY ) as SE_FAULTY label = '28 @Status of Equipments-Faulty', sum(a.SE_DISPOSED ) as SE_DISPOSED label = '28 @Status of Equipments-To be disposed', sum(a.SE_SURPLUS ) as SE_SURPLUS label = '28 @Status of Equipments-Suplus', sum(a.BP_IND ) as BP_IND label = '29 @Indigenous-Breedable Population', sum(a.BP_EXOTIC ) as BP_EXOTIC label = '29 @Exotic-Breedable Population', sum(a.BP_CROSSBRED ) as BP_CROSSBRED label = '29 @Crossbred-Breedable Population', sum(a.BP_BUFF ) as BP_BUFF label = '29 @Buffaloe-Breedable Population' from trans_sub_a_prog a group by a.REG_CODE, a.DIST_CODE, a.YEAR order by YEAR,REFERENCE_YEAR ; quit; proc sql noprint; select * from trans_sub_a_prog_sum; quit; %if &sqlobs = 0 %then %do; proc sql; insert into trans_sub_a_prog_sum (DIST_CODE, MONTH, YEAR) values(&dist,&month,&year) ; quit; %end; proc sql; create table trans_sub_b_prog as select b.REG_CODE, b.DIST_CODE, b.MONTH, case when MONTH between 4 and 12 then (month-3) else (month+9) end as REF_MONTH, b.YEAR, case when MONTH between 4 and 12 then YEAR else (YEAR+1) end as REF_YEAR, case when MONTH between 1 and 12 then cat(put(YEAR,4.),'-',substr(put((YEAR+1),4.),3,2)) end as REFERENCE_YEAR, sum(b.AI_F_INDIGENOUS ) as AI_F_INDIGENOUS label = '1(a) @AI Fresh-Indigenous', sum(b.AI_F_EXOTIC ) as AI_F_EXOTIC label = '1(a) @AI Fresh-Exotic', sum(b.AI_F_CROSSBRED ) as AI_F_CROSSBRED label = '1(a) @AI Fresh-Crossbred', sum(b.AI_F_BUFFALOE ) as AI_F_BUFFALOE label = '1(a) @AI Fresh-Buffaloe', sum(b.AI_R_INDIGENOUS ) as AI_R_INDIGENOUS label = '1(a) @AI (Repeat1+2)-Indigenous', sum(b.AI_R_EXOTIC ) as AI_R_EXOTIC label = '1(a) @AI (Repeat1+2)-Exotic', sum(b.AI_R_CROSSBRED ) as AI_R_CROSSBRED label = '1(a) @AI (Repeat1+2)-Crossbred', sum(b.AI_R_BUFFALOE ) as AI_R_BUFFALOE label = '1(a) @AI (Repeat1+2)-Buffaloe', sum(b.HQ_IND ) as HQ_IND label = '1(b) @AI HQ-Indigenous', sum(b.HQ_EXOTIC ) as HQ_EXOTIC label = '1(b) @AI HQ-Exotic', sum(b.HQ_CROSSBRED ) as HQ_CROSSBRED label = '1(b) @AI HQ-Crossbred', sum(b.HQ_BUFF ) as HQ_BUFF label = '1(b) @AI HQ-Buffaloe', sum(b.TOUR_IND ) as TOUR_IND label = '1(b) @AI on Tour-Indigenous', sum(b.TOUR_EXOTIC ) as TOUR_EXOTIC label = '1(b) @AI on Tour-Exotic', sum(b.TOUR_CROSSBRED ) as TOUR_CROSSBRED label = '1(b) @AI on Tour-Crossbred', sum(b.TOUR_BUFF ) as TOUR_BUFF label = '1(b) @AI on Tour-Buffaloe', sum(b.WC_IND ) as WC_IND label = '1(c) @AI Completed at Work Camp-Indigenous', sum(b.WC_EXOTIC ) as WC_EXOTIC label = '1(c) @AI Completed at Work Camp-Exotic', sum(b.WC_CROSSBRED ) as WC_CROSSBRED label = '1(c) @AI Completed at Work Camp-Crossbred', sum(b.WC_BUFF ) as WC_BUFF label = '1(c) @AI Completed at Work Camp-Buffaloe', sum(b.CB_INDIGENOUS ) as CB_INDIGENOUS label = '2 @Calves Born-Indigenous', sum(b.CB_CROSSBRED ) as CB_CROSSBRED label = '2 @Calves Born-Crossbred', sum(b.CB_BUFFALOE ) as CB_BUFFALOE label = '2 @Calves Born-Buffaloe', sum(b.PD_COWS ) as PD_COWS label = '3 @P.D.-Cows', sum(b.PD_BUFFALOES ) as PD_BUFFALOES label = '3 @P.D.-Buffaloe', sum(b.CASTRATIONS ) as CASTRATIONS label = '7 @Total Castrations- ', sum(b.VAC_HS ) as VAC_HS label = '9 @Vaccinations-HS', sum(b.VAC_BQ ) as VAC_BQ label = '9 @Vaccinations-BQ', sum(b.VAC_HSPLUSBQ ) as VAC_HSPLUSBQ label = '9 @Vaccinations-HS+BQ', sum(b.VAC_ETV_COW_BUFFALOE ) as VAC_ETV_COW_BUFFALOE label = '9 @Vaccinations-ETV(Cows and Buffalloes)', sum(b.VAC_FMD ) as VAC_FMD label = '9 @Vaccinations-FMD', sum(b.VAC_POX_COW_BUFFALOE ) as VAC_POX_COW_BUFFALOE label = '9 @Vaccinations-POX(Cows and Buffalloes)', sum(b.VAC_ETV_SHEEP_GOAT ) as VAC_ETV_SHEEP_GOAT label = '9 @Vaccinations-ETV (Sheep and Goat)', sum(b.VAC_POX_SHEEP_GOAT ) as VAC_POX_SHEEP_GOAT label = '9 @Vaccinations-Sheep Pox', sum(b.VAC_PPR ) as VAC_PPR label = '9 @Vaccinations-PPR', sum(b.VAC_ANTHRAX ) as VAC_ANTHRAX label = '9 @Vaccinations-Anthrax', sum(b.VAC_ANTIRABIES ) as VAC_ANTIRABIES label = '9 @Vaccinations-Anti Rabies', sum(b.VAC_FOWPOX ) as VAC_FOWPOX label = '9 @Vaccinations-Fowl Pox', sum(b.VAC_RANIKHET ) as VAC_RANIKHET label = '9 @Vaccinations-Ranikhet', sum(b.VAC_LASOTA ) as VAC_LASOTA label = '9 @Vaccinations-Lasota', sum(b.VAC_MAREKS ) as VAC_MAREKS label = '9 @Vaccinations-Mareks', sum(b.VAC_GAMBORO ) as VAC_GAMBORO label = '9 @Vaccinations-Gumboro', sum(b.VAC_OTHER ) as VAC_OTHER label = '9 @Vaccinations-Other' from DES_SDB.JOS_MPR_DISPENSARY_SUBORDINATE b where b.DIST_CODE = &dist and calculated REF_MONTH <= &fin_month and b.YEAR = &year group by b.REG_CODE, b.DIST_CODE, b.MONTH, b.YEAR order by REF_YEAR, MONTH ; quit; proc sql; create table trans_sub_b_prog_sum as select distinct b.REG_CODE, b.DIST_CODE, &month as Month, b.YEAR, b.REFERENCE_YEAR, sum(b.AI_F_INDIGENOUS ) as AI_F_INDIGENOUS label = '1(a) @AI Fresh-Indigenous', sum(b.AI_F_EXOTIC ) as AI_F_EXOTIC label = '1(a) @AI Fresh-Exotic', sum(b.AI_F_CROSSBRED ) as AI_F_CROSSBRED label = '1(a) @AI Fresh-Crossbred', sum(b.AI_F_BUFFALOE ) as AI_F_BUFFALOE label = '1(a) @AI Fresh-Buffaloe', sum(b.AI_R_INDIGENOUS ) as AI_R_INDIGENOUS label = '1(a) @AI (Repeat1+2)-Indigenous', sum(b.AI_R_EXOTIC ) as AI_R_EXOTIC label = '1(a) @AI (Repeat1+2)-Exotic', sum(b.AI_R_CROSSBRED ) as AI_R_CROSSBRED label = '1(a) @AI (Repeat1+2)-Crossbred', sum(b.AI_R_BUFFALOE ) as AI_R_BUFFALOE label = '1(a) @AI (Repeat1+2)-Buffaloe', sum(b.HQ_IND ) as HQ_IND label = '1(b) @AI HQ-Indigenous', sum(b.HQ_EXOTIC ) as HQ_EXOTIC label = '1(b) @AI HQ-Exotic', sum(b.HQ_CROSSBRED ) as HQ_CROSSBRED label = '1(b) @AI HQ-Crossbred', sum(b.HQ_BUFF ) as HQ_BUFF label = '1(b) @AI HQ-Buffaloe', sum(b.TOUR_IND ) as TOUR_IND label = '1(b) @AI on Tour-Indigenous', sum(b.TOUR_EXOTIC ) as TOUR_EXOTIC label = '1(b) @AI on Tour-Exotic', sum(b.TOUR_CROSSBRED ) as TOUR_CROSSBRED label = '1(b) @AI on Tour-Crossbred', sum(b.TOUR_BUFF ) as TOUR_BUFF label = '1(b) @AI on Tour-Buffaloe', sum(b.WC_IND ) as WC_IND label = '1(c) @AI Completed at Work Camp-Indigenous', sum(b.WC_EXOTIC ) as WC_EXOTIC label = '1(c) @AI Completed at Work Camp-Exotic', sum(b.WC_CROSSBRED ) as WC_CROSSBRED label = '1(c) @AI Completed at Work Camp-Crossbred', sum(b.WC_BUFF ) as WC_BUFF label = '1(c) @AI Completed at Work Camp-Buffaloe', sum(b.CB_INDIGENOUS ) as CB_INDIGENOUS label = '2 @Calves Born-Indigenous', sum(b.CB_CROSSBRED ) as CB_CROSSBRED label = '2 @Calves Born-Crossbred', sum(b.CB_BUFFALOE ) as CB_BUFFALOE label = '2 @Calves Born-Buffaloe', sum(b.PD_COWS ) as PD_COWS label = '3 @P.D.-Cows', sum(b.PD_BUFFALOES ) as PD_BUFFALOES label = '3 @P.D.-Buffaloe', sum(b.CASTRATIONS ) as CASTRATIONS label = '7 @Total Castrations- ', sum(b.VAC_HS ) as VAC_HS label = '9 @Vaccinations-HS', sum(b.VAC_BQ ) as VAC_BQ label = '9 @Vaccinations-BQ', sum(b.VAC_HSPLUSBQ ) as VAC_HSPLUSBQ label = '9 @Vaccinations-HS+BQ', sum(b.VAC_ETV_COW_BUFFALOE ) as VAC_ETV_COW_BUFFALOE label = '9 @Vaccinations-ETV(Cows and Buffalloes)', sum(b.VAC_FMD ) as VAC_FMD label = '9 @Vaccinations-FMD', sum(b.VAC_POX_COW_BUFFALOE ) as VAC_POX_COW_BUFFALOE label = '9 @Vaccinations-POX(Cows and Buffalloes)', sum(b.VAC_ETV_SHEEP_GOAT ) as VAC_ETV_SHEEP_GOAT label = '9 @Vaccinations-ETV (Sheep and Goat)', sum(b.VAC_POX_SHEEP_GOAT ) as VAC_POX_SHEEP_GOAT label = '9 @Vaccinations-Sheep Pox', sum(b.VAC_PPR ) as VAC_PPR label = '9 @Vaccinations-PPR', sum(b.VAC_ANTHRAX ) as VAC_ANTHRAX label = '9 @Vaccinations-Anthrax', sum(b.VAC_ANTIRABIES ) as VAC_ANTIRABIES label = '9 @Vaccinations-Anti Rabies', sum(b.VAC_FOWPOX ) as VAC_FOWPOX label = '9 @Vaccinations-Fowl Pox', sum(b.VAC_RANIKHET ) as VAC_RANIKHET label = '9 @Vaccinations-Ranikhet', sum(b.VAC_LASOTA ) as VAC_LASOTA label = '9 @Vaccinations-Lasota', sum(b.VAC_MAREKS ) as VAC_MAREKS label = '9 @Vaccinations-Mareks', sum(b.VAC_GAMBORO ) as VAC_GAMBORO label = '9 @Vaccinations-Gumboro', sum(b.VAC_OTHER ) as VAC_OTHER label = '9 @Vaccinations-Other' from trans_sub_b_prog b group by b.REG_CODE, b.DIST_CODE,b.YEAR order by YEAR,REFERENCE_YEAR ; quit; proc sql noprint; select * from trans_sub_b_prog_sum; quit; %if &sqlobs = 0 %then %do; proc sql; insert into trans_sub_b_prog_sum (DIST_CODE, MONTH, YEAR) values(&dist,&month,&year) ; quit; %end; data ab_join_prog_sum; marge trans_sub_a_prog_sum trans_sub_b_prog_sum; run; proc sql noprint; select * from ab_join_prog_sum; quit; %if &sqlobs = 0 %then %do; proc sql; insert into ab_join_prog_sum (DIST_CODE, MONTH, YEAR) values(&dist,&month,&year) ; quit; %end; PROC TRANSPOSE DATA=ab_join_prog_sum OUT=WORK.ab_join_trans_prog_sum PREFIX=Column NAME=Source LABEL=Label; BY REG_CODE DIST_CODE MONTH YEAR REFERENCE_YEAR ; VAR BP_IND BP_EXOTIC BP_CROSSBRED BP_BUFF AI_F_INDIGENOUS AI_F_EXOTIC AI_F_CROSSBRED AI_F_BUFFALOE AI_R_INDIGENOUS AI_R_EXOTIC AI_R_CROSSBRED AI_R_BUFFALOE HQ_IND HQ_EXOTIC HQ_CROSSBRED HQ_BUFF TOUR_IND TOUR_EXOTIC TOUR_CROSSBRED TOUR_BUFF WC_IND WC_EXOTIC WC_CROSSBRED WC_BUFF CB_INDIGENOUS CB_CROSSBRED CB_BUFFALOE PD_COWS PD_BUFFALOES LN2_CONSUMED INFERTILITY_COWS INFERTILITY_BUFFALOES TREATMENT CASTRATIONS OPERATIONS_MAJOR OPERATIONS_MINOR VAC_HS VAC_BQ VAC_HSPLUSBQ VAC_ETV_COW_BUFFALOE VAC_FMD VAC_POX_COW_BUFFALOE VAC_ETV_SHEEP_GOAT VAC_POX_SHEEP_GOAT VAC_PPR VAC_ANTHRAX VAC_ANTIRABIES VAC_FOWPOX VAC_RANIKHET VAC_LASOTA VAC_MAREKS VAC_GAMBORO VAC_OTHER SEED_DISTRIBUTION SAPPLINGS_DISTRIBUTION MINIKIT WORKCAMP CALF_RALLIES SET_MICROSCOPE SET_HAEMATOLOGICAL SET_BIOCHEMICAL EGG_HATCH_DISTRIBUTION DAY_OLD_CHICKS DIST_PULLETS DIST_MILCH_CATTLE DIST_MILCH_BUFF DIST_SHEEP DIST_GOAT PM_NC_LARGEANIMALS PM_NC_SMALLANIMALS PM_NC_BIRDS PM_VL_LARGEANIMALS PM_VL_SMALLANIMALS PM_VL_BIRDS PM_DG_LARGEANIMALS PM_DG_SMALLANIMALS PM_DG_BIRDS MILKCOMP_BLOCK MILKCOMP_STATE MILKCOMP_NATIONAL SONOGRAPHY XRAY SERVICE_CHARGES SC_MLDB SE_WORKING SE_FAULTY SE_DISPOSED SE_SURPLUS ; RUN; QUIT; data s1_prog_sum; set ab_join_trans_prog_sum; sr=_n_; run; proc sql; create table Final as select a.*, b.column1 as column2 , c.column1 as column3 from s1 a left join trans1_tar b on a.source= b.source left join s1_prog_sum c on a.source=c.source order by a.sr; quit; proc sql; Create Table Final_trans as select *, scan(label,1,'@') as sr1, scan(scan(label,-1,'@'),1,'-') as Category, scan(scan(label,-1,'@'),-1,'-') as Item from final order by sr; quit; data yr noprint; yr=put(&year,$8.); run; PROC SQL NOPRINT; SELECT 'Month:' || strip(put(Month_no, 2.)) as asd INTO :dep_valu from DES_SDB.MONTH_ENG where month_no = &month ; select STRIP(yr) as yr into : yer from yr; QUIT; %let yr = %sysfunc(strip(&yer)); %let dep_value = %sysfunc(strip(&dep_valu)); %put &dep_value &Dist &REPORT_TYPE &table &yr; proc sql noprint; select count(1) into : count from DES_SDB.REPORT_MASTER_LIST_DIM where STRIP(REPORT_TYPE) = "&REPORT_TYPE" and TABLE_NAME = "&table" and DISTRICT_ID = %bquote(&Dist) and DEPENDENT_VALUE = "&dep_value" and STRIP(REPORTING_YEAR) = "&yr" ; quit; %put &count; %if &count > 0 %then %do; proc sql; connect to oracle(user=&uid orapw=&pwd path = &path); execute( update REPORT_MASTER_LIST_DIM set REPORT_TYPE = 'ADF', TABLE_NO ='1_1_1', TABLE_NAME = 'ADF_1_1_1', TYPE_ID = 2, FORMAT_ID = null, SECTOR_ID = 1, DEPT_ID = 1, GEO_ID = null, REGION_ID = null, DISTRICT_ID = %bquote(&Dist), TALUKA_ID = null, REPORT_HTML_HREF = %bquote('/&districtname/HTML/&yr._&districtname._&month_name._ADF_1_1_1.html'), REPORT_PDF_HREF = %bquote('/&districtname/PDF/&yr._&districtname._&month_name._ADF_1_1_1.pdf'), REPORT_XLS_HREF = %bquote('/&districtname/XLS/&yr._&districtname._&month_name._ADF_1_1_1.xls'), REPORTING_YEAR = %bquote('&yr'), STATE_LEVEL = 'N', TITLE = %bquote('&rpt_title'), DEPENDENT_VALUE = %bquote('&dep_value'), LAST_UPDATED_DT = sysdate where TABLE_NAME = %bquote('&table') and DISTRICT_ID = %bquote(&Dist) and REPORTING_YEAR = %bquote('&yr') and DEPENDENT_VALUE like %bquote('&dep_value')) by oracle; disconnect from oracle; quit; %end; %else %do; proc sql; connect to oracle(user=&uid orapw=&pwd path = &path); execute(insert into REPORT_MASTER_LIST_DIM (ID,REPORT_TYPE,TABLE_NO,TABLE_NAME,TYPE_ID,FORMAT_ID,SECTOR_ID,DEPT_ID,GEO_ID,REGION_ID, DISTRICT_ID,TALUKA_ID,REPORT_HTML_HREF,REPORT_PDF_HREF,REPORT_XLS_HREF,REPORTING_YEAR,STATE_LEVEL,TITLE,DEPENDENT_VALUE,LAST_UPDATED_DT) values(RPT_MST_DIM_SEQ.NEXTVAL,'ADF','1_1_1',%bquote('&table'),2,null,1,1,null,null,%bquote(&Dist),null, %bquote('/&districtname/HTML/&yr._&districtname._&month_name._ADF_1_1_1.html'),%bquote('/&districtname/PDF/&yr._&districtname._&month_name._ADF_1_1_1.pdf'), %bquote('/&districtname/XLS/&yr._&districtname._&month_name._ADF_1_1_1.xls'),%bquote('&yr'),'N',%bquote('&rpt_title'),%bquote('&dep_value'), sysdate) ) by oracle; disconnect from oracle; quit; %end; /*---------------------------------------Insert Row Code for District End---------------------------------------*/ title1 j = c h=6 "&rpt_title"; title3 j = l h=4 "District : &districtname" ; title4 j = l h=4 "Year : &REFERENCE_YEAR" j=right "Month : &month_name"; title5 j = r h=4 "Report : Monthly" ; /*footnote4 j=r 'page *{thispage} of *{lastpage}';*/ Data a; set FINAL_TRANS; if column1 = . then if column3 = . then ct=0; else ct=1; else ct=1; ; run; proc sql noprint; select sum(ct) as ct into : ct from a ; quit; %if &ct=0 %then %do; data b (Keep=message); Message="Data Not Available"; run; proc print data=b noobs; run; %end; %else %do; footnote; PROC TABULATE DATA=WORK.FINAL_TRANS OUT=FINAL_TR; where sr1<>'29'; VAR Column1 sr /*column2*/ column3; CLASS sr1 / ORDER=Data MISSING; CLASSLEV sr1 / S=[background=White VJUST=MIDDLE fontweight=light] ; CLASS Category / ORDER=Data MISSING; CLASSLEV Category / S=[background=White VJUST=MIDDLE fontweight=light] ; CLASS Item / ORDER=Data MISSING; CLASSLEV Item / S=[background=White VJUST=MIDDLE fontweight=light] ; TABLE /* Row Dimension */ sr1 = {LABEL="Sr No."} * Category = {LABEL="Activities"} * Item = {LABEL="Type"}, /* Column Dimension */ /* Column2={LABEL="Target" STYLE={FONT_WEIGHT=BOLD }}* F=NA. * Sum={LABEL="" STYLE=[FONT_WEIGHT= BOLD FONT_SIZE=15 FONT_FACE="Arial"]} */ column1={LABEL="Activities Performed (Number)" STYLE={FONT_WEIGHT=BOLD FONT_STYLE=ROMAN }}* F=NA. * Sum={LABEL=" " STYLE=[FONT_WEIGHT= BOLD FONT_SIZE=3 FONT_FACE="Arial"]} Column3={LABEL="Progressive Activities Performed (Number)" STYLE={FONT_WEIGHT=BOLD }}* F=NA. * Sum={LABEL=" " STYLE=[FONT_WEIGHT= BOLD FONT_SIZE=3 FONT_FACE="Arial"]}; ; RUN; title; footnote; ods pdf startpage=yes; title1 j = c h=6 "Total Breedable Population in District"; title3 j = l h=4 "District : &districtname" ; title4 j = l h=4 "Year : &REFERENCE_YEAR" j=right "Month : &month_name"; title5 j = r h=4 "Report : Monthly" ; PROC TABULATE DATA=WORK.FINAL_TRANS OUT=FINAL_TR; where sr1='29'; VAR Column1 /*sr*/ column3; /* CLASS sr1 / ORDER=Data MISSING;*/ CLASSLEV sr1 / S=[background=White VJUST=MIDDLE fontweight=light] ; CLASS Category / ORDER=Data MISSING; CLASSLEV Category / S=[background=White VJUST=MIDDLE fontweight=light] ; /* CLASS Item / ORDER=Data MISSING;*/ /* CLASSLEV Item / S=[background=White VJUST=MIDDLE fontweight=light] ; */ TABLE /* Row Dimension */ Category = {LABEL="Activities"}, /* sr1 = {LABEL="Sr No."} * Category * Item,*/ /* Column Dimension */ column1={LABEL="Total (Number)" STYLE={FONT_WEIGHT=BOLD FONT_STYLE=ROMAN }}* F=NA. * Sum={LABEL='' STYLE=[FONT_WEIGHT= BOLD FONT_SIZE=15 FONT_FACE="Vardhana"]} Column3={LABEL="Progressive (Number)" STYLE={FONT_WEIGHT=BOLD }}* F=NA. * Sum={LABEL="" STYLE=[FONT_WEIGHT= BOLD FONT_SIZE=15 FONT_FACE="Arial"]}; ; footnote1 j = l h=4 "Source: Department of Animal Husbandry, Government of Maharashtra"; footnote2 j = l h=4 "Navigation Path : www.mahasdb.maharashtra.gov.in > Home >Departments > ADF >Standard Reports"; footnote3 j = l h=4 "This report should be updated within 7 days after completion of Reporting period"; RUN; %end; %end; ods _all_ Close; title; footnote; Proc datasets lib=work kill nolist; quit; run; %mend report; %report;