/* --- Start of code for "AP GN3". --- */
%include "/SASCODE/MPSIMS/SAS_Lib_Details/sdb_templates.sas";
%include "/SASCODE/MPSIMS/SAS_Lib_Details/mpsims_temp.sas";

%mcr_lib;

options mlogic mprint symbolgen;

/*ods _ALL_ close; */
ods _ALL_ close; 
ods listing;
 
%macro mcr_generateData;
/*%let plan=&plan;*/
/*%let dept=&dept;*/
/*%let year= &year;*/
/*%let lang= ⟨*/




%let plan=0;
%let dept=47;
%let year= 2016;
%let lang= ENG;

%if &lang = MAR %then %do;
	%let lang_c= _MAR;
	%let lang_sch= NM_MAR;
	%let lang_ss= _marathi;
	%let lang_srf= MAR_new;
	%let lang_obj = _MAR;
%end;
%else %do;
	%let lang_c= ;
	%let lang_sch= Name;
	%let lang_ss= ;
	%let lang_srf= ;
	%let lang_obj = _ENG;
%end;


%let plan_c= ;
%let dept_c= ;
%let plan_cc= ;
%let dept_cc= ;
%let pln_name= All;

%let p_year = %eval(&year-1);
%let pp_year = %eval(&year-2);

%let yr = %substr(%eval(&year+1),3,2);
%let p_yr = %substr(%eval(&p_year+1),3,2);
%let pp_yr = %substr(%eval(&pp_year+1),3,2);

%LET ENG_YR=&YEAR-&YR;
%LET P_ENG_YR=&P_YEAR-&P_YR;
%LET PP_ENG_YR=&PP_YEAR-&PP_YR;

%if &plan ne 0 %then %do;
	%let plan_c= AND sr.plan_type_id = &plan ;
	%let plan_cc= AND plan_type = &plan ;
	proc sql NOPRINT ;
		select  PLAN_TYPE_NM_&lang into :pln_name from NWSAPORA.PLAN_TYPE WHERE PLAN_TYPE_CD =put(&plan,1.) ;
	quit;
%end;

%if &dept ne 0 %then %do;
	%let dept_c= AND SR.DEPT_CODE = &dept;
	%let dept_cc= AND DEPT_CD = &dept;
%end;

%put &plan_c &dept_c;

data _null_; 
set NWSAPORA.SAS_LABLES; 
 if id eq 1 then call symputx("department",LABLE_NAME_&lang);
 if id eq 2 then call symputx("sector",LABLE_NAME_&lang);
 if id eq 3 then call symputx("subsector",LABLE_NAME_&lang);
 if id eq 5 then call symputx("secttot",LABLE_NAME_&lang);
 if id eq 6 then call symputx("subsectortot",LABLE_NAME_&lang);
 if id eq 7 then call symputx("Scheme",LABLE_NAME_&lang);
 if id eq 12 then call symputx("SrNo",LABLE_NAME_&lang);
 if id eq 16 then call symputx("fyrplan",LABLE_NAME_&lang);
 if id eq 21 then call symputx("RsLkh",LABLE_NAME_&lang);
 if id eq 22 then call symputx("prooutlay",LABLE_NAME_&lang);
 if id eq 23 then call symputx("Actexp",LABLE_NAME_&lang);
 if id eq 24 then call symputx("Appoutlay",LABLE_NAME_&lang);
 if id eq 25 then call symputx("Antiexp",LABLE_NAME_&lang);
 if id eq 35 then call symputx("Projoutlay",LABLE_NAME_&lang);
 if id eq 45 then call symputx("target",LABLE_NAME_&lang);
 if id eq 46 then call symputx("achvmnt",LABLE_NAME_&lang);
 if id eq 48 then call symputx("antiachvmnt",LABLE_NAME_&lang);
 if id eq 50 then call symputx("NmOfScheme",LABLE_NAME_&lang);
 if id eq 85 then call symputx("title1",LABLE_NAME_&lang);
 if id eq 242 then call symputx("title2",LABLE_NAME_&lang);
 if id eq 141 then call symputx("Total",LABLE_NAME_&lang);
 if id eq 271 then call symputx("Sccd",LABLE_NAME_&lang);
 if id eq 272 then call symputx("bucd",LABLE_NAME_&lang);
 if id eq 273 then call symputx("Obnm",LABLE_NAME_&lang);
 if id eq 285 then call symputx("catitem",LABLE_NAME_&lang);
 if id eq 290 then call symputx("proptar",LABLE_NAME_&lang);
 if id eq 329 then call symputx("unit",LABLE_NAME_&lang);
 if id eq 614 then call symputx("ap1516",LABLE_NAME_&lang);
 if id eq 504 then call symputx("plan1217",LABLE_NAME_&lang); 
  if id eq 631 then call symputx("AnnualPlan",LABLE_NAME_&lang);
 if id eq 545 then call symputx("ap1314",LABLE_NAME_&lang); 
 if id eq 587 then call symputx("ap1415",LABLE_NAME_&lang); 
 IF STRIP(LABLE_NAME_ENG) eq "&ENG_YR" then call symputx("LBL_YR_MAR", LABLE_NAME_&lang);
 IF STRIP(LABLE_NAME_ENG) eq "&P_ENG_YR" then call symputx("LBL_PYR_MAR", LABLE_NAME_&lang);
 IF STRIP(LABLE_NAME_ENG) eq "&PP_ENG_YR" then call symputx("LBL_PPYR_MAR", LABLE_NAME_&lang);

RUN;

proc format; 
value dot_to_zero .=0.00 
				  0=0.00 
	          other=[14.2]; 
value dot_to_dot .=. 
				  0=0.00 
	          other=[14.2]; 
quit; 

PROC SQL;
	CREATE TABLE GN3_pre AS 
	SELECT DISTINCT  
		SR.DEPT_CODE, DEPT.DEPT_NAME&lang_c as DEPT_NAME, SR.SECTOR_CODE, 	SEC.SECTOR_NAME&lang_c as SECTOR_NAME, SR.SUBSECTCODE, 
		SS.SUB_SECTOR_NAME&lang_ss as SUB_SECTOR_NAME,	 SCH.SCHEME_&lang_sch as SCHEME_NAME,
		OM.OBJECT_NAME&lang_obj AS OBJECT_NAME_ENG,  (trim(CM.CATEGORY_NAME&lang_obj)||' '||trim(IM.ITEM_NAME&lang_obj)) AS CATNAME, UM.UNIT_NAME&lang_obj as UNIT_NAME_ENG,
		SR.record_submit_status, sr.sap_record_id, 
	/*	sr.plan_year_id,*/
		SR.NEW_GENERATED_SCH_CD,  SR.OBJECT_CODE , 
	/*	substr(sr.budget_code,5,1) as budget_code, */
		((case when sr.record_submit_status=3 then GN.TARGET_FYP end))AS TARGET_FYP,
		((case when sr.PLAN_YEAR_ID = &pp_year AND GN.EXECUTION_PLAN_YEAR = &pp_year &rss3 then GN.TARGET end))AS TAR_PP,
		((case when sr.PLAN_YEAR_ID = &year AND GN.EXECUTION_PLAN_YEAR = &pp_year &rss then GN.ANTICIPATED_ACHIEVEMENT end))AS ACH_PP,
		((case when sr.PLAN_YEAR_ID = &p_year AND GN.EXECUTION_PLAN_YEAR = &p_year &rss3 then GN.TARGET end))AS TAR_P,
		((case when sr.PLAN_YEAR_ID = &year AND GN.EXECUTION_PLAN_YEAR = &p_year &rss then GN.ANTICIPATED_ACHIEVEMENT end))AS ACH_P,
		((case when sr.PLAN_YEAR_ID = &year AND GN.EXECUTION_PLAN_YEAR = &year &rss then GN.TARGET end))AS TARGET	 	
	FROM NWSAPORA.V_SAS_SRBIBOI SR
		INNER JOIN NWSAPORA.OBJECT_ITEM_INFO AS OII ON (SR.SAP_RECORD_ID = OII.SAP_RECORD_ID)
		AND(SR.PLAN_YEAR_ID = OII.PLAN_YEAR_ID) AND (SR.OBJECT_CODE = OII.OBJECT_CODE)
		FULL JOIN NWSAPORA.GN3_DETAILS AS GN ON (OII.OBJECT_ITEM_INFO_ID = GN.OBJECT_ITEM_INFO_ID) 
		INNER JOIN NWSAPORA.OBJECT_MST AS OM ON (SR.OBJECT_CODE = OM.OBJECT_CODE)
		INNER JOIN NWSAPORA.DEPARTMENT AS DEPT ON (SR.DEPT_CODE = DEPT.DEPT_CD)  
		INNER JOIN NWSAPORA.SCHEME AS SCH ON (SR.SCHEME_TITLE_CODE = SCH.SCHEME_ID)  
		INNER JOIN NWSAPORA.SECTOR AS SEC ON (SR.SECTOR_CODE = SEC.SECTOR_CD)  
		INNER JOIN NWSAPORA.SUB_SECTOR AS SS ON (SR.SUBSECTCODE = SS.SUB_SECTOR_CD) 
		INNER JOIN NWSAPORA.CATEGORY_MST AS CM ON (OII.CATEGORY_ID = CM.CATEGORY_ID)  
		INNER JOIN NWSAPORA.ITEM_MST AS IM ON (OII.ITEM_REF_ID = IM.ITEM_ID)  
		INNER JOIN NWSAPORA.UNIT_MST AS UM ON (OII.UNIT_REF_ID = UM.UNIT_ID)  
	WHERE SR.PLAN_YEAR_ID BETWEEN 2012 AND &year AND SR.SCHEME_LEVEL = "State Plan" and DEPT_CODE not in (44,45,.)
		&plan_c &dept_c 
		AND NEW_GENERATED_SCH_CD IS NOT NULL AND OM.OBJECT_CODE NOT IN (3, 4, 6, 13, 18,20, 24, 41, 63)
		;	
QUIT;


PROC SQL;
	CREATE TABLE gn3 AS
	SELECT * FROM GN3_pre 	
	WHERE (TARGET_FYP NOT in (0,.)) OR (TAR_PP NOT in (0,.)) OR (ACH_PP NOT in (0,.)) 
		OR (TAR_P NOT in (0,.)) OR (ACH_P NOT in (0,.)) OR (TARGET NOT in (0,.))
	;
QUIT;  


proc sql;
	create table gn3 as
	select DEPT_CODE, DEPT_NAME, SECTOR_CODE,  SECTOR_NAME, SUBSECTCODE,  SUB_SECTOR_NAME, 
SCHEME_NAME,
		OBJECT_NAME_ENG,  CATNAME, UNIT_NAME_ENG, NEW_GENERATED_SCH_CD,  OBJECT_CODE ,
		sum((TARGET_FYP))AS TARGET_FYP, sum((TAR_PP))AS TAR_PP, sum((ACH_PP))AS ACH_PP,
		sum((TAR_P))AS TAR_P, sum((ACH_P))AS ACH_P, sum((TARGET))AS TARGET 
	from gn3
	group by DEPT_CODE, DEPT_NAME, SECTOR_CODE, SECTOR_NAME, SUBSECTCODE, SUB_SECTOR_NAME, SCHEME_NAME,
		OBJECT_NAME_ENG,  CATNAME, UNIT_NAME_ENG, NEW_GENERATED_SCH_CD,  OBJECT_CODE 
	ORDER BY DEPT_CODE, SECTOR_CODE,  SUBSECTCODE,  NEW_GENERATED_SCH_CD
	;
quit;


proc sort data = gn3 ;
BY DEPT_Code SECTOR_CODE SUBSECTCODE  NEW_GENERATED_SCH_CD SCHEME_NAME ;
run;

data gn3 ;
set gn3;
by DEPT_Code  SECTOR_CODE SUBSECTCODE  NEW_GENERATED_SCH_CD SCHEME_NAME ;
	retain sr ;	
if first.DEPT_Code then  sr=1;
else if first.NEW_GENERATED_SCH_CD then sr=sr+1;
else sr = sr;
run;

%include "/SASCODE/MPSIMS/SAS_Lib_Details/ExcelTemplate.txt";

%stpbegin;

/* EXCEL Output */
  filename csvout catalog "&_tmpcat..document.csv";
  ods TAGSETS.MSOFFICE2K  file=csvout style=mystyle;


ods markup file="//SASCODE/MPSIMS/excel_output/GN3_Marathi_&plan._&dept._&year..xls"  tagset=tagsets.test alias='normal'  style=styles.mystyle;

proc report data=WORK.GN3  nocompletecols missing split='*' 
	style(header)=[font=(Arial,10pt,bold)]
	style(LINES)=[font=(Arial,10pt,bold)]
	style(report)=[BORDERCOLORLIGHT=DMYELLOW]	 ; 

column 		DEPT_CODE ("&department:" DEPT_NAME) 
			SECTOR_CODE ("&sector: " SECTOR_NAME) 
			SUBSECTCODE ("&subsector : " SUB_SECTOR_NAME) 
		   ("&SrNo" "" "1" sr) 
		   ("&Sccd" "" "2" NEW_GENERATED_SCH_CD) 
		   ("&NmOfScheme" "" "3" SCHEME_NAME) 
			OBJECT_CODE ("&Obnm" "" "4" OBJECT_NAME_ENG) 
		   ("&catitem" "" "5" CATNAME) 
	        ("&unit" "" "6" UNIT_NAME_ENG ) 
			("&plan1217" ("&target" "7" TARGET_FYP=g)) 
			("&AnnualPlan &LBL_PPYR_MAR" (("&target" "8" TAR_PP=c)("&achvmnt" "9" ACH_PP=d))) 
			("&AnnualPlan &LBL_PYR_MAR" (("&target" "10" TAR_P=e)("&antiachvmnt" "11" ACH_P=f)))		
			("&AnnualPlan &LBL_YR_MAR" (("&proptar" "12" TARGET=h))) 
			; 
	define DEPT_CODE			/ "" GROUP  noprint; 
	define DEPT_NAME			/ "" GROUP  noprint; 
	define SECTOR_CODE			/ "" GROUP  noprint; 
	define SECTOR_NAME			/ "" GROUP  noprint; 
	define SUBSECTCODE			/ "" GROUP  noprint; 	
	define SUB_SECTOR_NAME		/ "" GROUP  noprint;
	define SR					/ "" GROUP order=data ;  
	define NEW_GENERATED_SCH_CD	/ "" GROUP  ; 
	define SCHEME_NAME			/ "" GROUP  ; 
	define OBJECT_CODE			/ "" GROUP noprint; 
	define OBJECT_NAME_ENG		/ "" GROUP ; 
	define CATNAME	/ "" GROUP; 
	define UNIT_NAME_ENG		/ "" GROUP ; 
	define g					/sum "" format=dot_to_zero.; 
	define c					/sum "" format=dot_to_zero.; 
	define d					/sum "" format=dot_to_zero. ; 
	define e					/sum "" format=dot_to_zero.; 
	define f					/sum "" format=dot_to_zero.; 	
	define h					/sum "" format=dot_to_zero.; 
	 
	compute before SUB_SECTOR_NAME; 
		DepartmentML = length(DEPT_NAME) ; 
		SectorML=length(SECTOR_NAME); 
		SUB_SECTOR_NAMEML=length(SUB_SECTOR_NAME); 
   
		line @1 "&department. :" DEPT_NAME $varying. DepartmentML  '   	                                  				'                    
					"&sector.: " SECTOR_NAME $varying. SectorML    '		                             	    		'                                 
					"&subsector.: " SUB_SECTOR_NAME $varying. SUB_SECTOR_NAMEML ;
		endcomp;	


	title1 "&title1"; 
	 
	title2 "&title2. - &pln_name"; 
 
run; 

%stpend; 

%mend;

%mcr_generateData;
/* --- End of code for "AP GN3". --- */