QSDB Step2 Crops COUNTY revenue 20220312.sas

/* Start timer */
%let _timer_start = %sysfunc(datetime());

DM "CLEAR LOG ; CLEAR OUTPUT ; ";
options source2 ls=80 ps=60 cpucount=8 fullstimer formchar="|----|+|---+=|-/\<>*";
/*************************************************************/
/************** NASS complete database **** Saleem, Shaik  ***/
Title 'NASS QSBD Saleem SHAIK - March, Oct 2021';
/*************************************************************/
/*
Saleem Shaik, Ph.D
Branch Chief
Agricultural Policy and Models Branch
ERS, USDA
E:Saleem.Shaik@usda.gov
*/



/*************************************************************/
/*************************************************************/
LIBNAME NASS2 'C:\Users\Saleem.Shaik\OneDrive - USDA\Saleem.Shaik\papers\dataforanalysis\NASS';
LIBNAME NASS3 'C:\Users\Saleem.Shaik\OneDrive - USDA\Saleem.Shaik\papers\dataforanalysis\SCP\domestic';

LIBNAME CLIM "C:\Users\Saleem.Shaik\OneDrive - USDA\Saleem.Shaik\papers\dataforanalysis\Climate";
LIBNAME CLIM1 "C:\Users\Saleem.Shaik\OneDrive - USDA\Saleem.Shaik\papers\analysis\climate";
/*************************************************************/
/*************************************************************/



/*
LIBNAME NASS1 "\\usda.net\ers\MTEDCOMMON\APM\STAFF\SShaik\US\data";
LIBNAME NASS2 "\\usda.net\ers\MTEDCOMMON\APM\STAFF\SShaik\US\dataforanalysis\NASS";
LIBNAME NASS3 "\\usda.net\ers\MTEDCOMMON\APM\STAFF\SShaik\US\dataforanalysis\SCP";

LIBNAME NASS1 'C:\Users\Saleem.Shaik\OneDrive - USDA\Saleem.Shaik\papers\dataforanalysis\NASS';
LIBNAME NASS2 'C:\Users\Saleem.Shaik\OneDrive - USDA\Saleem.Shaik\papers\dataforanalysis\SCP';
LIBNAME CLIM "C:\Users\Saleem.Shaik\OneDrive - USDA\Saleem.Shaik\papers\dataforanalysis\Climate";
LIBNAME CLIM1 "C:\Users\Saleem.Shaik\OneDrive - USDA\Saleem.Shaik\papers\analysis\climate";
*/



/*************************************************************/
%let yrmonday = 20220312.;
%let yrmonday1 = 20220312;
/*************************************************************/



/*************************************************************/
/*************************************************************/
data PRICE REVENUE PRODUCTION COUNTY_PRODUCTION check;
	set NASS2.step1_QS_Crops; 
	if STATISTICCAT_DESC IN ('PRODUCTION') AND 
		UNIT_DESC IN ('$', '$ / TON', '$, CHERRY BASIS', 
		'$, ON TREE EQUIV', '$, PHD EQUIV') 
		then STATISTICCAT_DESC = 'PRODUCTION in $';

	if AGG_LEVEL_DESC IN ('STATE', 'NATIONAL') AND
		STATISTICCAT_DESC IN ('PRICE RECEIVED') AND
		UTIL_PRACTICE_DESC IN ('ALL UTILIZATION PRACTICES',
		'GRAIN', 'SUGAR') then output PRICE; 

	if AGG_LEVEL_DESC IN ('STATE', 'NATIONAL') AND
		STATISTICCAT_DESC IN ('PRODUCTION in $') AND
		UTIL_PRACTICE_DESC IN ('ALL UTILIZATION PRACTICES',
		'GRAIN', 'SUGAR') AND 
		FREQ_DESC IN ('ANNUAL') then output REVENUE; 

	if AGG_LEVEL_DESC IN ('STATE', 'NATIONAL') AND
		STATISTICCAT_DESC IN ('PRODUCTION') AND
		UTIL_PRACTICE_DESC IN ('ALL UTILIZATION PRACTICES',
		'GRAIN', 'SUGAR') AND 
		FREQ_DESC IN ('ANNUAL') then output PRODUCTION; 

	if AGG_LEVEL_DESC IN ('COUNTY') AND
		STATISTICCAT_DESC IN ('PRODUCTION') AND
		UTIL_PRACTICE_DESC IN ('ALL UTILIZATION PRACTICES',
		'GRAIN', 'SUGAR') then output COUNTY_PRODUCTION;

	if AGG_LEVEL_DESC IN ('NATIONAL') AND
		COMMODITY_DESC IN ('CANOLA') then output check;
run;
/*************************************************************/
/*************************************************************/


  
/*************************************************************/
* Create STATE PRICE dataset;
/*************************************************************/
data PRICES_ST_1(keep=AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC 
		STATE_FIPS_CODE STATE_NAME 
		YEAR REFERENCE_PERIOD_DESC FREQ_DESC VALUE VALUE1);
	set PRICE;
	WHERE AGG_LEVEL_DESC IN ('STATE', 'NATIONAL') AND
			STATISTICCAT_DESC IN ('PRICE RECEIVED') AND 
			/*FREQ_DESC IN ('ANNUAL', 'MONTHLY') AND
			*/REFERENCE_PERIOD_DESC IN ('MARKETING YEAR', 'YEAR',
					'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN',
					'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC') AND
			UNIT_DESC NOTIN ('PCT', 'PCT BY COLOR', 'PCT BY GRADE',
					'PCT BY TYPE', 'PCT OF PARITY',
					'PCT OF PRODUCTION', 'PCT OF TOTAL STOCKS');
*	PRICES = value1;
*	PRICES_unit=UNIT_DESC;
run;
data PRICES_ST_Annual PRICES_ST_month;
	set PRICES_ST_1;
	if REFERENCE_PERIOD_DESC = 'MARKETING YEAR' then 
		REFERENCE_PERIOD_DESC = 'MARKETING YEAR PRICES';
	if REFERENCE_PERIOD_DESC = 'YEAR' then 
		REFERENCE_PERIOD_DESC = 'CALENDER YEAR PRICES';

	if REFERENCE_PERIOD_DESC IN ('MARKETING YEAR PRICES', 
		'CALENDER YEAR PRICES') then output PRICES_ST_Annual; 
	else if REFERENCE_PERIOD_DESC IN ('JAN', 'FEB', 'MAR', 
		'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 
		'OCT', 'NOV', 'DEC') then output PRICES_ST_month;
run;
proc sort data=PRICES_ST_Annual 
			DUPout=DUP_PRICES_ST_1_annual NODUPKEY 
			OUT=PRICES_ST_Annual_1;
	where FREQ_DESC in ('ANNUAL');
	by AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC 
		STATE_FIPS_CODE STATE_NAME 
		YEAR 
		REFERENCE_PERIOD_DESC;
run;
proc transpose data=PRICES_ST_Annual_1 out=PRICES_ST_Annual_2(drop=_NAME_);
	where FREQ_DESC in ('ANNUAL');
	id REFERENCE_PERIOD_DESC;
	idlabel REFERENCE_PERIOD_DESC;
	var VALUE1;
	by AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC 
		STATE_FIPS_CODE STATE_NAME 
		YEAR; 
run;
proc sort data=PRICES_ST_Month 
		DUPout=DUP_PRICES_ST_Month_1 NODUPKEY 
		OUT=PRICES_ST_Month_1;
	where FREQ_DESC notin ('ANNUAL');
	by AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC 
		STATE_FIPS_CODE STATE_NAME 
		YEAR
		REFERENCE_PERIOD_DESC;
run;
proc transpose data=PRICES_ST_Month_1 out=PRICES_ST_Month_2(drop=_NAME_) prefix=Price_;
	where FREQ_DESC NOTIN ('ANNUAL');
	id REFERENCE_PERIOD_DESC;
	idlabel REFERENCE_PERIOD_DESC;
	var VALUE1;
	by AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC 
		STATE_FIPS_CODE STATE_NAME 
		YEAR;
run;
data PRICES_ST_3;
	retain Merge_ST_price;
	merge PRICES_ST_Annual_2(in=b) PRICES_ST_Month_2(in=a);
	by AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC 
		STATE_FIPS_CODE STATE_NAME 
		YEAR;
	if a and b then Merge_ST_price='Annual and Monthly'; else
	if a then Merge_ST_price='Annual'; else
	if b then Merge_ST_price='Monthly'; 
run;
proc freq data=PRICES_ST_3; table Merge_ST_price; run;
data PRICES_ST_4;
	set PRICES_ST_3;
	JD_PRICES_N= N(of Price_JAN, Price_FEB, Price_MAR,
						Price_APR, Price_MAY, Price_JUN,
						Price_JUL, Price_AUG, Price_SEP,
						Price_OCT, Price_NOV, Price_DEC);
	if JD_PRICES_N>3 then 
	JD_PRICES= mean(of Price_JAN, Price_FEB, Price_MAR,
						Price_APR, Price_MAY, Price_JUN,
						Price_JUL, Price_AUG, Price_SEP,
						Price_OCT, Price_NOV, Price_DEC);
	proc sort; 
	by AGG_LEVEL_DESC COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC
		STATE_FIPS_CODE STATE_NAME YEAR;
run;
data PRICES_ST_5(Keep=Merge_ST_price 
		AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC PRICES_unit
		STATE_FIPS_CODE STATE_NAME YEAR
		MARKETING_YEAR_PRICES MARKETING_YEAR_PRICES1 
		MARKETING_YEAR_PRICES2 CALENDER_YEAR_PRICES 
		JD_PRICES JD_PRICES_N
		Price_JAN Price_FEB Price_MAR 
		Price_APR Price_MAY Price_JUN 
		Price_JUL Price_AUG Price_SEP 
		Price_OCT Price_NOV Price_DEC);
	retain Merge_ST_price 
		AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC PRICES_unit
		STATE_FIPS_CODE STATE_NAME YEAR
		MARKETING_YEAR_PRICES MARKETING_YEAR_PRICES1 
		MARKETING_YEAR_PRICES2 CALENDER_YEAR_PRICES 
		JD_PRICES JD_PRICES_N
		Price_JAN Price_FEB Price_MAR 
		Price_APR Price_MAY Price_JUN 
		Price_JUL Price_AUG Price_SEP 
		Price_OCT Price_NOV Price_DEC; 
	set PRICES_ST_4;
	if MARKETING_YEAR_PRICES = . then 
	MARKETING_YEAR_PRICES1=CALENDER_YEAR_PRICES; else
	MARKETING_YEAR_PRICES1=MARKETING_YEAR_PRICES;
	if MARKETING_YEAR_PRICES1 = . then 
	MARKETING_YEAR_PRICES2=JD_PRICES; else
	MARKETING_YEAR_PRICES2=MARKETING_YEAR_PRICES1;
	PRICES_unit=UNIT_DESC;
	proc sort; 
	by AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC PRICES_unit
		STATE_FIPS_CODE STATE_NAME YEAR;
run;
data PRICES_ST_6(Keep=Merge_ST_price 
		AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC PRICES_unit
		STATE_FIPS_CODE STATE_NAME YEAR
		MARKETING_YEAR_PRICES MARKETING_YEAR_PRICES1
		MARKETING_YEAR_PRICES2);
	set PRICES_ST_5;
	proc sort; 
	by AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC PRICES_unit
		STATE_FIPS_CODE STATE_NAME YEAR;
run;
data PRICES_ST_7(Keep=Merge_ST_price 
		AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC PRICES_unit
		STATE_FIPS_CODE STATE_NAME YEAR
		MARKETING_YEAR_PRICES MARKETING_YEAR_PRICES1
		MARKETING_YEAR_PRICES2);
	set PRICES_ST_6;
	proc sort; 
	by AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC PRICES_unit
		STATE_FIPS_CODE STATE_NAME YEAR;
run;
data PRICES_ST_8(Keep=Merge_ST_price 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC UTIL_PRACTICE_DESC 
		PRICES_unit_st
		STATE_FIPS_CODE STATE_NAME YEAR
		MARKETING_YEAR_PRICES MARKETING_YEAR_PRICES1
		MARKETING_YEAR_PRICES2);
	set PRICES_ST_7;
	PRICES_unit_st=PRICES_unit;
	proc sort; 
	by COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC UTIL_PRACTICE_DESC 
		STATE_FIPS_CODE STATE_NAME YEAR;
run;
/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/







/*************************************************************/
* Create STATE REVENUE dataset;
/*************************************************************/
data REVENUE_ST_1(keep=AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC 
		STATE_FIPS_CODE STATE_NAME 
		YEAR REFERENCE_PERIOD_DESC FREQ_DESC VALUE VALUE1);
	set REVENUE;
	WHERE REFERENCE_PERIOD_DESC IN ('YEAR');
run;
data REVENUE_ST_2(Keep=AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC UTIL_PRACTICE_DESC 
		REVENUE_unit
		STATE_FIPS_CODE STATE_NAME YEAR
		REVENUE);
	set REVENUE_ST_1;
	REVENUE = value1;
	REVENUE_unit =UNIT_DESC;
	proc sort; 
	by AGG_LEVEL_DESC COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC UTIL_PRACTICE_DESC 
		STATE_FIPS_CODE STATE_NAME YEAR;
run;

data PRODUCTION_ST_1(keep=AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC 
		STATE_FIPS_CODE STATE_NAME 
		YEAR REFERENCE_PERIOD_DESC FREQ_DESC VALUE VALUE1);
	set PRODUCTION;
	WHERE REFERENCE_PERIOD_DESC IN ('YEAR');
run;
data PRODUCTION_ST_2(Keep=AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		PRODUCTION_unit
		STATE_FIPS_CODE STATE_NAME YEAR
		PRODUCTION);
	set PRODUCTION_ST_1;
	PRODUCTION = value1;
	PRODUCTION_unit =UNIT_DESC;
	proc sort; 
	by AGG_LEVEL_DESC COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC UTIL_PRACTICE_DESC 
		STATE_FIPS_CODE STATE_NAME YEAR;
run;
/*************************************************************/
/*************************************************************/









data PRODUCTION_REVENUE;
	retain Merge_ST_PRODREV;
	merge PRODUCTION_ST_2(in=a) REVENUE_ST_2(in=b);
	by AGG_LEVEL_DESC COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC UTIL_PRACTICE_DESC 
		STATE_FIPS_CODE STATE_NAME YEAR;
	if a and b then Merge_ST_PRODREV='PRODUCTION & REVENUE'; else
	if a then Merge_ST_PRODREV='PRODUCTION'; else
	if b then Merge_ST_PRODREV='REVENUE'; 
run;
proc freq data=PRODUCTION_REVENUE; 
table COMMODITY_DESC*Merge_ST_PRODREV/norow nocol nocum nopercent; 
run;
data PRODUCTION_REVENUE_1;
	set PRODUCTION_REVENUE;
	if PRODUCTION>0 and REVENUE>0 then 
		PRICES=REVENUE/PRODUCTION;
run;
data PRODUCTION_REVENUE_2(keep=AGG_LEVEL_DESC 
	COMMODITY_GROUP_code COMMODITY_GROUP 
	SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
	CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
	STATE_FIPS_CODE STATE_NAME YEAR
	PRODUCTION PRODUCTION_unit
	REVENUE REVENUE_unit
	PRICES);
	set PRODUCTION_REVENUE_1;
run;
proc sort data=PRODUCTION_REVENUE_2 
		DUPout=DUP_PRODUCTION_REVENUE_2 NODUPKEY 
		OUT=PRODUCTION_REVENUE_3;
	by COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATE_FIPS_CODE STATE_NAME YEAR AGG_LEVEL_DESC
		PRODUCTION REVENUE ;
run;
/*************************************************************/
/*************************************************************/
















/*************************************************************/
* Flag 5D - Create STATE PRICES dataset;
/*************************************************************/
/*************************************************************/
/*************************************************************/
data PRICES_US_1(keep=AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC 
		STATE_FIPS_CODE STATE_NAME 
		YEAR REFERENCE_PERIOD_DESC FREQ_DESC VALUE VALUE1);
	set PRICE;
	WHERE AGG_LEVEL_DESC IN ('NATIONAL') AND
			STATISTICCAT_DESC IN ('PRICE RECEIVED') AND 
			/*FREQ_DESC IN ('ANNUAL', 'MONTHLY') AND
			*/REFERENCE_PERIOD_DESC IN ('MARKETING YEAR', 'YEAR',
					'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN',
					'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC') AND
			UNIT_DESC NOTIN ('PCT', 'PCT BY COLOR', 'PCT BY GRADE',
					'PCT BY TYPE', 'PCT OF PARITY',
					'PCT OF PRODUCTION', 'PCT OF TOTAL STOCKS');
*	PRICES = value1;
*	PRICES_unit=UNIT_DESC;
run;
/*
proc means data=PRICES_US_1_US mean noprint;
class COMMODITY_DESC CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC FREQ_DESC REFERENCE_PERIOD_DESC YEAR;
types () COMMODITY_DESC*CLASS_DESC*PRODN_PRACTICE_DESC*UTIL_PRACTICE_DESC*FREQ_DESC*REFERENCE_PERIOD_DESC*YEAR;
var VALUE1;
output out=sumstat_US;
run;
data sumstat_US_a; set sumstat_US; where _STAT_='MEAN';run;
*/
data PRICES_US_Annual PRICES_US_month;
	set PRICES_US_1;
	if REFERENCE_PERIOD_DESC = 'MARKETING YEAR' then 
		REFERENCE_PERIOD_DESC = 'MARKETING YEAR PRICES';
	if REFERENCE_PERIOD_DESC = 'YEAR' then 
		REFERENCE_PERIOD_DESC = 'CALENDER YEAR PRICES';

	if REFERENCE_PERIOD_DESC IN ('MARKETING YEAR PRICES', 
		'CALENDER YEAR PRICES') then output PRICES_US_Annual; 
	else if REFERENCE_PERIOD_DESC IN ('JAN', 'FEB', 'MAR', 
		'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 
		'OCT', 'NOV', 'DEC') then output PRICES_US_month;
run;
proc sort data=PRICES_US_Annual 
			DUPout=DUP_PRICES_US_1_annual NODUPKEY 
			OUT=PRICES_US_Annual_1;
	where FREQ_DESC in ('ANNUAL');
	by AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC 
		STATE_FIPS_CODE STATE_NAME 
		YEAR 
		REFERENCE_PERIOD_DESC;
run;
proc transpose data=PRICES_US_Annual_1 out=PRICES_US_Annual_2(drop=_NAME_);
	where FREQ_DESC in ('ANNUAL');
	id REFERENCE_PERIOD_DESC;
	idlabel REFERENCE_PERIOD_DESC;
	var VALUE1;
	by AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC 
		STATE_FIPS_CODE STATE_NAME 
		YEAR; 
run;
proc sort data=PRICES_US_Month 
		DUPout=DUP_PRICES_US_Month_1 NODUPKEY 
		OUT=PRICES_US_Month_1;
	where FREQ_DESC notin ('ANNUAL');
	by AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC 
		STATE_FIPS_CODE STATE_NAME 
		YEAR
		REFERENCE_PERIOD_DESC;
run;
proc transpose data=PRICES_US_Month_1 out=PRICES_US_Month_2(drop=_NAME_) prefix=Price_;
	where FREQ_DESC NOTIN ('ANNUAL');
	id REFERENCE_PERIOD_DESC;
	idlabel REFERENCE_PERIOD_DESC;
	var VALUE1;
	by AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC 
		STATE_FIPS_CODE STATE_NAME 
		YEAR;
run;
data PRICES_US_3;
	retain Merge_US_price;
	merge PRICES_US_Annual_2(in=b) PRICES_US_Month_2(in=a);
	by AGG_LEVEL_DESC
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC 
		STATE_FIPS_CODE STATE_NAME 
		YEAR;
	if a and b then Merge_US_price='Annual and Monthly'; else
	if a then Merge_US_price='Annual'; else
	if b then Merge_US_price='Monthly'; 
run;
proc freq data=PRICES_US_3; table Merge_US_price; run;
data PRICES_US_4;
	set PRICES_US_3;
	JD_PRICES_N= N(of Price_JAN, Price_FEB, Price_MAR,
						Price_APR, Price_MAY, Price_JUN,
						Price_JUL, Price_AUG, Price_SEP,
						Price_OCT, Price_NOV, Price_DEC);
	if JD_PRICES_N>3 then 
	JD_PRICES= mean(of Price_JAN, Price_FEB, Price_MAR,
						Price_APR, Price_MAY, Price_JUN,
						Price_JUL, Price_AUG, Price_SEP,
						Price_OCT, Price_NOV, Price_DEC);
	proc sort; 
	by AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC UNIT_DESC
		STATE_FIPS_CODE STATE_NAME YEAR;
run;
data PRICES_US_5(Keep=Merge_US_price 
		AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC PRICES_unit
		STATE_FIPS_CODE STATE_NAME YEAR
		MARKETING_YEAR_PRICES MARKETING_YEAR_PRICES1 
		MARKETING_YEAR_PRICES2 CALENDER_YEAR_PRICES 
		JD_PRICES JD_PRICES_N
		Price_JAN Price_FEB Price_MAR 
		Price_APR Price_MAY Price_JUN 
		Price_JUL Price_AUG Price_SEP 
		Price_OCT Price_NOV Price_DEC);
	retain Merge_US_price 
		AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC PRICES_unit
		STATE_FIPS_CODE STATE_NAME YEAR
		MARKETING_YEAR_PRICES MARKETING_YEAR_PRICES1 
		MARKETING_YEAR_PRICES2 CALENDER_YEAR_PRICES 
		JD_PRICES JD_PRICES_N
		Price_JAN Price_FEB Price_MAR 
		Price_APR Price_MAY Price_JUN 
		Price_JUL Price_AUG Price_SEP 
		Price_OCT Price_NOV Price_DEC; 
	set PRICES_US_4;
	if MARKETING_YEAR_PRICES = . then 
	MARKETING_YEAR_PRICES1=CALENDER_YEAR_PRICES; else
	MARKETING_YEAR_PRICES1=MARKETING_YEAR_PRICES;
	if MARKETING_YEAR_PRICES1 = . then 
	MARKETING_YEAR_PRICES2=JD_PRICES; else
	MARKETING_YEAR_PRICES2=MARKETING_YEAR_PRICES1;
	PRICES_unit=UNIT_DESC;
	proc sort; 
	by AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC PRICES_unit
		STATE_FIPS_CODE STATE_NAME YEAR;
run;
data PRICES_US_6(Keep=Merge_US_price 
		AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC PRICES_unit
		STATE_FIPS_CODE STATE_NAME YEAR
		MARKETING_YEAR_PRICES MARKETING_YEAR_PRICES1
		MARKETING_YEAR_PRICES2);
	set PRICES_US_5;
	proc sort; 
	by AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC PRICES_unit
		STATE_FIPS_CODE STATE_NAME YEAR;
run;
data PRICES_US_7(Keep=Merge_US_price 
		AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC PRICES_unit
		STATE_FIPS_CODE STATE_NAME YEAR
		MARKETING_YEAR_PRICES MARKETING_YEAR_PRICES1
		MARKETING_YEAR_PRICES2);
	set PRICES_US_6;
	proc sort; 
	by AGG_LEVEL_DESC 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC 
		STATISTICCAT_DESC PRICES_unit
		STATE_FIPS_CODE STATE_NAME YEAR;
run;
data PRICES_US_8(Keep=Merge_US_price 
		COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC UTIL_PRACTICE_DESC 
		YEAR PRICES_unit_us
		MARKETING_YEAR_PRICES_US MARKETING_YEAR_PRICES1_US
		MARKETING_YEAR_PRICES2_US);
	set PRICES_US_7;
	PRICES_unit_us=PRICES_unit;
	MARKETING_YEAR_PRICES_US=MARKETING_YEAR_PRICES; 
	MARKETING_YEAR_PRICES1_US=MARKETING_YEAR_PRICES1;
	MARKETING_YEAR_PRICES2_US=MARKETING_YEAR_PRICES2;
	proc sort; 
	by COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC UTIL_PRACTICE_DESC 
		YEAR;
run;
/*************************************************************/
/*************************************************************/
/*
proc sql; 
	create table PRICES_USST_7a  as
	select *, coalesce(PRICES_ST_7a.groupID, PRICES_US_7a.groupID) as USSTid
  	from PRICES_ST_7a full join PRICES_US_7a on 
	PRICES_ST_7a.groupID=PRICES_US_7a.groupID;
quit;
*/



/*************************************************************/
/*************************************************************/
data COUNTY_PRODUCTION_1;
	set COUNTY_PRODUCTION;
	proc sort; 
	by COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC UTIL_PRACTICE_DESC 
		STATE_FIPS_CODE STATE_NAME YEAR;
run;
data COUNTY_REVENUE;
	retain Merge_cost;
	merge COUNTY_PRODUCTION_1(in=a) PRICES_ST_8(in=b);
	by COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC UTIL_PRACTICE_DESC 
		STATE_FIPS_CODE STATE_NAME YEAR;
	if a and b then Merge_cost='CO_production and ST_prices'; 
	else if a then Merge_cost='CO_production'; 
	else if b then Merge_cost='ST_prices';
run;
proc freq data=COUNTY_REVENUE; table Merge_cost; run;
data COUNTY_REVENUE_1;
	set COUNTY_REVENUE;
	where Merge_cost IN ('CO_production and ST_prices',
						'CO_production');
run;
data COUNTY_REVENUE_A;
	retain Merge_costrev;
	merge COUNTY_REVENUE(in=a) REVENUE_ST_1(in=b);
	by COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC UTIL_PRACTICE_DESC 
		STATE_FIPS_CODE STATE_NAME YEAR;
	if a and b then Merge_costrev='CO_production and ST_revenue'; 
	else if a then Merge_costrev='CO_production'; 
	else if b then Merge_costrev='ST_ST_revenue';
run;
proc freq data=COUNTY_REVENUE_A; table Merge_costrev; run;
data COUNTY_REVENUE_1;
	set COUNTY_REVENUE_A;
	where Merge_cost IN ('CO_production and ST_revenue',
						'CO_production');
	proc sort; 
	by COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC UTIL_PRACTICE_DESC 
		YEAR;
run;
data COUNTY_REVENUE_2;
	retain Merge_cous Merge_cost;
	merge COUNTY_REVENUE_1(in=a) PRICES_US_8(in=b);
	by COMMODITY_GROUP_code COMMODITY_GROUP 
		SOURCE_DESC SECTOR_DESC GROUP_DESC COMMODITY_DESC 
		CLASS_DESC UTIL_PRACTICE_DESC 
		YEAR;
	if a and b then Merge_cous='CO_production and US_prices'; 
	else if a then Merge_cous='CO_production'; 
	else if b then Merge_cous='US_prices';
run;
data COUNTY_REVENUE_3;
	set COUNTY_REVENUE_2;
	where Merge_cous IN ('CO_production and US_prices',
						'CO_production');
run;
data COUNTY_REVENUE_4(keep=Merge_cous Merge_cost 
		Merge_ST_price Merge_US_price
		AGG_LEVEL_DESC 
		COMMODITY_GROUP COMMODITY_GROUP_CODE 
		SOURCE_DESC SECTOR_DESC GROUP_DESC 
		COMMODITY_DESC CLASS_DESC PRODN_PRACTICE_DESC 
		UTIL_PRACTICE_DESC STATISTICCAT_DESC SHORT_DESC 
		STATE_ANSI STATE_FIPS_CODE STATE_ALPHA STATE_NAME 
		ASD_CODE ASD_DESC COUNTY_ANSI COUNTY_CODE COUNTY_NAME 
		LOCATION_DESC YEAR FREQ_DESC REFERENCE_PERIOD_DESC 
		VALUE1 UNIT_DESC MARKETING_YEAR_PRICES2 PRICES_unit_st
		MARKETING_YEAR_PRICES2_US PRICES_unit_us
		REVENUE_unit_st REVENUE_st);
	retain Merge_cous Merge_cost 
		Merge_ST_price Merge_US_price
		AGG_LEVEL_DESC 
		COMMODITY_GROUP COMMODITY_GROUP_CODE 
		SOURCE_DESC SECTOR_DESC GROUP_DESC 
		COMMODITY_DESC CLASS_DESC PRODN_PRACTICE_DESC 
		UTIL_PRACTICE_DESC STATISTICCAT_DESC SHORT_DESC 
		STATE_ANSI STATE_FIPS_CODE STATE_ALPHA STATE_NAME 
		ASD_CODE ASD_DESC COUNTY_ANSI COUNTY_CODE COUNTY_NAME 
		LOCATION_DESC YEAR FREQ_DESC REFERENCE_PERIOD_DESC 
		VALUE1 UNIT_DESC MARKETING_YEAR_PRICES2 PRICES_unit_st
		MARKETING_YEAR_PRICES2_US PRICES_unit_us 
		REVENUE_unit_st REVENUE_st;
	set COUNTY_REVENUE_3;
	proc sort; 
	by AGG_LEVEL_DESC 
		COMMODITY_GROUP COMMODITY_GROUP_CODE 
		SOURCE_DESC SECTOR_DESC GROUP_DESC 
		COMMODITY_DESC CLASS_DESC PRODN_PRACTICE_DESC 
		UTIL_PRACTICE_DESC STATISTICCAT_DESC SHORT_DESC 
		STATE_ANSI STATE_FIPS_CODE STATE_ALPHA STATE_NAME 
		ASD_CODE ASD_DESC COUNTY_ANSI COUNTY_CODE COUNTY_NAME 
		LOCATION_DESC YEAR FREQ_DESC REFERENCE_PERIOD_DESC; 
run;
/*
proc freq data=COUNTY_REVENUE_4; 
table COMMODITY_DESC*(UNIT_DESC CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC)/norow nocol nocum nopercent out=FreqCount outexpect sparse; 
run;
proc freq data=COUNTY_REVENUE_4; 
table COMMODITY_DESC*PRICES_unit_st/norow nocol nocum nopercent out=FreqCount_1 outexpect sparse; 
run;
proc freq data=COUNTY_REVENUE_4; 
table (COMMODITY_DESC CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC)*PRICES_unit_st/norow nocol nocum nopercent out=FreqCount_1 outexpect sparse; 
run;
proc freq data=COUNTY_REVENUE_4; 
table COUNTY_CODE*COMMODITY_DESC/norow nocol nocum nopercent out=FreqCount outexpect sparse; 
run;
proc means data=COUNTY_REVENUE_4 mean noprint;
class COMMODITY_DESC CLASS_DESC PRODN_PRACTICE_DESC UTIL_PRACTICE_DESC Merge_cous YEAR COUNTY_ANSI;
types () COMMODITY_DESC*CLASS_DESC*PRODN_PRACTICE_DESC*UTIL_PRACTICE_DESC*Merge_cous*YEAR*COUNTY_ANSI;
var MARKETING_YEAR_PRICES2 MARKETING_YEAR_PRICES2_US;
output out=sumstat;
run;
data sumstat_a; set sumstat; where _STAT_='MEAN';run;
*/ 
/*************************************************************/
/*************************************************************/
data COUNTY_REVENUE_5 COUNTY_REVENUE_5_998;
	set COUNTY_REVENUE_4;
	if COMMODITY_DESC IN ('SORGHUM') then 
		REVENUE=(VALUE1/1.786)*MARKETING_YEAR_PRICES2; else
	if COMMODITY_DESC IN ('SUNFLOWER', 'CANOLA', 'SAFFLOWER') then 
		REVENUE=(VALUE1/100)*MARKETING_YEAR_PRICES2; else
	if COMMODITY_DESC IN ('COTTON') then
		REVENUE=(VALUE1*480)*MARKETING_YEAR_PRICES2; else
	REVENUE=(VALUE1)*MARKETING_YEAR_PRICES2; 
	if COUNTY_CODE=998 then output COUNTY_REVENUE_5_998;
	else output COUNTY_REVENUE_5;
run;
data COUNTY_REVENUE_5_check;
	set COUNTY_REVENUE_5;
	where COMMODITY_DESC IN ('SORGHUM', 'SUNFLOWER', 
	'CANOLA', 'SAFFLOWER', 'COTTON'); 
run;
/*************************************************************/
/*************************************************************/








/********************************************************/
/********************************************************/
data NASS2.Step1_PRICES_US_ST; set PRICES_8; run;
data NASS2.Step1_COUNTY_REVENUE; set COUNTY_REVENUE; run;
/********************************************************/
/********************************************************/




/* Stop timer */
data _null_;
  dur = datetime() - &_timer_start;
  put 30*'-' / ' TOTAL DURATION:' dur time13.2 / 30*'-';
run;