*------------------------------------------------------------------------------------*
This is the program file for Tables 1-8 in "Comparing Manufacturing Export Growth 
Across States: What Accounts for the Differences?" by Cletus C. Coughlin and Patricia 
S. Pollard in the January/February 2001 issue of the Federal Reserve Bank of St. 
Louis Review. This file contains a) programs for creating the data libraries used in 
tables 1 through 8 and b) programs to produce the results shown in tables 1 through 8.
The export data used to produce all of the tables in this article are proprietary and 
are available in a summarized form upon request.
*------------------------------------------------------------------------------------*;



filename in2 'd:\sas\state_exports\rawdata\SIC_2digit';
filename in 'd:\sas\shift_share\rawdata\gsp';
libname out 'd:\sas\exportsgsp_88to98';
***----------------------------------------------------------------------------------------------------------------***;
***                                        NOMINAL MISER EXPORTS LIBRARY                                           ***;
***----------------------------------------------------------------------------------------------------------------***;
*--1988 to 1995--*;
%macro first8;
  %do i = 88 %to 95;
  data nom&i(keep=state country sic x idvar);
    infile in2(st&i.om) firstobs=1;
    input @1 state $2. @3 sic 2. @5 country 4.   x 12. junk1-junk6;

    **CREATE YEAR VARIABLE FOR TIME SERIES**;
      idvar="19&i";
    **KEEP ONLY MANUFACTURING INDUSTRIES**;
      if 20<=sic<=39;
    **KEEP 50 STATES AND DC**;
      if state not in("PR","VI");
  run;
%end;
%mend;
%first8;

*--1996 to 1998--*;
%macro last3;
  %do i = 96 %to 98;
  data nom&i(keep=state country sic x idvar);
    infile in2(st&i.om) firstobs=1;
    input @2 state $2. @7 sic 2. @12 country 4.  @18 x;

    **CREATE YEAR VARIABLE FOR TIME SERIES**;
      idvar="19&i";
    **KEEP ONLY MANUFACTURING INDUSTRIES**;
      if 20<=sic<=39;
    **KEEP 50 STATES AND DC**;
      if state not in("PR","VI");
  run;
%end;
%mend;
%last3;

*--COMBINE ALL YEARS OF NOMINAL DATA--*;
data out.x_n;
  set nom88 nom89 nom90 nom91 nom92 nom93 nom94 nom95 nom96 nom97 nom98;
  rename x=x_n;
run;



***----------------------------------------------------------------------------------------------------------------***;
***                                         NOMINAL GSP DATA LIBRARY                                               ***;
***----------------------------------------------------------------------------------------------------------------***;
data gsp_n (drop=id comp gsp1977-gsp1987 fips statenm);
  infile in(gspall_n) firstobs=2 dlm="," linesize=1000;
  input @2 id 3. @8 comp $60. @72 FIPS 2. @82 statenm $27. @112 code 5.
        @152 gsp1977 gsp1978-gsp1997;
  **TOTAL GSP**;
    if code=0;
  **KEEP ONLY 50 STATES AND DC**;
    if fips=0 or fips>90 then delete;
  **CREATE STATE POSTAL CODE;
    state=fipstate(fips);
run;

data gsp98_n(drop=statenm fips);
  infile in(gsp98_n) firstobs=6 dlm="," linesize=1000;
  input fips 2. statenm $ col1;

  **KEEP ONLY 50 STATES AND DC**;
    if fips=0 or fips>90 then delete;
  **CREATE STATE POSTAL CODE;
    state=fipstate(fips);

  _name_="gsp1998";
run;

**MAKE SINGLE VAR OUT OF GSP88-GSP98**;
proc sort data=gsp_n; by state; run;
proc sort data=gsp98_n out=gsp98; by state; run;

proc transpose data=gsp_n out=gsp;
  var gsp1988-gsp1997;
  by state;
run;

data gsp_n(drop=_name_);
  set gsp gsp98;
  idvar=substr(_name_,4,4);
  rename col1=gsp_n;
run;

proc sort data=gsp_n out=out.gsp_n; by state idvar; run;




***----------------------------------------------------------------------------------------------------------------***;
***                                        NOMINAL EXPORT GROWTH BY STATE                                          ***;
***                                        POLLARD/COUGHLIN TABLE 1, COLUMN 1                                      ***;
***----------------------------------------------------------------------------------------------------------------***;
libname _all_ clear; filename _all_ clear;
libname in 'd:\sas\exportsgsp_88to98';
options nocenter nodate nonumber linesize=100 pagesize=100;


**SHRINK EXPORT DATA SET -- KEEP ONLY 1988 AND 1998**;
data levels;
  set in.x_n;
  if idvar in("1988","1998");
run;


**CREATE EXPORTS BY YEAR AND STATE**;
proc sort data=levels; by idvar state; run;
proc means data=levels sum noprint;
  var x_n;
  by idvar state;
  output out=levels_a sum=;
run;


**MAKE COLUMN FOR EACH YEAR OF EXPORTS BY STATE**;
proc sort data=levels_a; by state; run;
proc transpose data=levels_a out=levels_t;
  var x_n;
  by state;
  id idvar;
  idlabel idvar;
run;


**CALCULATE PERCENTAGE CHANGE IN NOMINAL EXPORTS BY STATE**;
**BETWEEN 1988 AND 1998**;
data changen(drop=_1988-_1998 state);
  set levels_t(where=(_name_<>""));
  drop _name_;

  ch_8898n = (((_1998/_1988)**.1)-1)*100;
  staten = stnamel(state);
run;


**PRINT OUT RESULTS IN TABLE FORMAT**;
proc sort data=changen; by staten; run;
proc print data=changen noobs;
  var staten ch_8898n;
  format ch_8898n 5.1;
  title "TABLE 1";
  title2 "State Manufacturing Exports";
  title3 "Nominal";
  title4 "Annual Growth Rate 1988-1998 (percent)";
run;


***----------------------------------------------------------------------------------------------------------------***;
***                                     NOMINAL EXPORTS AS SHARE OF US TOTAL BY STATE                              ***;
***                                        POLLARD/COUGHLIN TABLE 1, COLUMN 2                                      ***;
***----------------------------------------------------------------------------------------------------------------***;
libname in 'd:\sas\exportsgsp_88to98';
options nocenter nodate nonumber linesize=122 pagesize=67;


**SHRINK EXPORT DATA SET -- KEEP ONLY 1988 AND 1998**;
data x;
  set in.x_n;
  if idvar in ("1988","1998");
run;


**CREATE EXPORTS BY YEAR AND STATE**;
proc sort data=x; by idvar state; run;
proc means data=x sum noprint;
  var x_n;
  by idvar state;
  output out=x_s sum=x;
run;


**CREATE EXPORTS BY YEAR TO GET US TOTAL**;
proc sort data=x; by idvar; run;
proc means data=x sum noprint;
  var x_n;
  by idvar;
  output out=x_t sum=xt;
run;


**COMBINE STATE AND US EXPORTS BY YEAR -- MAKE STATE SHARE OF TOTAL**;
data combine;
  merge x_s x_t;
  by idvar;
  share=(x/xt)*100;
run;


**MAKE COLUMN FOR EACH YEAR OF SHARE OF EXPORTS BY STATE**;
proc sort data=combine; by state; run;
proc transpose data=combine out=printer1;
  var share;
  by state;
  id idvar;
  idlabel idvar;
run;
data nom1;
  set printer1(drop=_name_);
  rename _1988=_1988n;
  rename _1998=_1998n;
run;


**MAKE COLUMN FOR EACH YEAR OF EXPORT LEVELS BY STATE**;
proc transpose data=combine out=printer2;
  var x;
  by state;
  id idvar;
  idlabel idvar;
run;
data nom2;
  set printer2(drop=_name_);
  rename _1988=Level88;
  rename _1998=Level98;
run;


**COMBINE COLUMN LEVELS AND COLUMN SHARES BY STATE**;
data nom(drop=state);
  merge nom1 nom2;
  by state;
  staten = stnamel(state);
  Level98=Level98/1000000;
run;


**PRINT OUT RESULTS IN TABLE FORMAT**;
proc sort data=nom; by staten; run;
proc print data=nom noobs;
  format _1998n 7.2 format Level98 comma9.1;
  var staten Level98 _1998n;
  sum Level98 _1998n;
  title "TABLE 1";
  title2 "State Manufacturing Exports";
  title3 "Nominal";
  title4 "Share of National Manufacturing Exports 1998 (percent)";
run;



***----------------------------------------------------------------------------------------------------------------***;
***                                     NOMINAL EXPORTS AS SHARE OF TOTAL GSP BY STATE                             ***;
***                                                POLLARD/COUGHLIN TABLE 2                                        ***;
***----------------------------------------------------------------------------------------------------------------***;
libname in 'd:\sas\exportsgsp_88to98';
options nocenter nodate nonumber linesize=122 pagesize=67;

**SHRINK EXPORT DATA SET -- KEEP ONLY 1988 AND 1997**;
data x;
  set in.x_n;
  if idvar in ("1988","1998");
run;


**CREATE EXPORTS BY YEAR AND STATE**;
proc sort data=x; by idvar state; run;
proc means data=x sum noprint;
  var x_n;
  by idvar state;
  output out=x_s sum=;
run;


**READ IN TOTAL GSP DATA**;
data gsp;
  set in.gsp_n;
run;
proc sort data=gsp; by idvar state; run;


**COMBINE MANUFACTURING EXPORTS AND TOTAL GSP BY STATE**;
**CALCULATE RATIO OF EXPORTS TO TOTAL GSP BY STATE**;
data combine;
  merge gsp x_s;
  by idvar state;

  ratio = ((x_n/1000000)/gsp_n)*100;
  if idvar in ("1988","1998");
run;

**MAKE COLUMN FOR EACH YEAR OF EXPORTS/GSP BY STATE**;
proc sort data=combine; by state; run;
proc transpose data=combine out=printer;
  var ratio;
  by state;
  id idvar;
  idlabel idvar;
run;


**CALCULATE FIRST DIFFERENCE OF 1998 AND 1988 RATIOS**;
data printer (keep=staten _1988 _1998 diff);
  set printer(drop=_name_);
  diff = _1998-_1988;
  staten = stnamel(state);
run;


**PRINT OUT RESULTS IN TABLE FORMAT**;
proc sort data=printer; by staten; run;
proc print data=printer noobs;
  var staten _1998 _1988 diff;
  format _1998 _1988 diff 12.2;
  title "TABLE 2";
  title2 "Manufacturing Exports as Share of Gross State Product - Nominal";
run;

proc print data=printer(where=(_1998>=10)) noobs;
  var staten _1998;
  title "TABLE 2 - Col. 1,  Bold";
run;

proc print data=printer(where=(0<=_1998<=1)) noobs;
  var staten _1998;
  title "TABLE 2 - Col. 1, Italics";
run;

proc print data=printer(where=(0<=diff>=3)) noobs;
  var staten diff;
  title "TABLE 2 - Col. 3,  Bold";
run;




***----------------------------------------------------------------------------------------------------------------***;
***                                     COMPOUND ANNUAL EXPORT GROWTH BY INDUSTRY                                  ***;
***                                             POLLARD/COUGHLIN TABLE 3                                           ***;
***----------------------------------------------------------------------------------------------------------------***;
libname in 'd:\sas\exportsgsp_88to98';
options nocenter nodate nonumber linesize=122 pagesize=67;


**SHRINK EXPORT DATA SET -- KEEP ONLY 1988 AND 1998**;
data levels;
  set in.x_n(where=(idvar in("1988","1998")));
run;


*---BY SIC---*;
**CREATE EXPORTS BY YEAR AND SIC**;
proc sort data=levels; by sic; run;
proc means data=levels(where=(idvar="1988")) sum noprint;
  var x_n;
  by sic;
  output out=x88 sum=x88;
run;
proc means data=levels(where=(idvar="1998")) sum noprint;
  var x_n;
  by sic;
  output out=x98 sum=x98;
run;


**COMBINE 1988 AND 1998 EXPORTS BY SIC**;
proc sort data=x88; by sic; run;
proc sort data=x98; by sic; run;
data combine(drop=_type_ _freq_);
  merge x88 x98;
  by sic;
  **COMPOUND ANNUAL CHANGE from 1988 to 1998**;
    change = (((x98/x88)**.1)-1)*100;
run;


**PRINT OUT RESULTS IN TABLE FORMAT**;
proc print data=combine noobs;
  var sic change;
  format change 5.1;
  title "TABLE 3";
  title2 "Export Growth by Industry 1988-1998";
  title3 "Compound Annual Rate (percent)";
  title4 "By SIC";
run;



*---TOTAL---*;
**CREATE EXPORTS BY YEAR**;
proc means data=levels(where=(idvar="1988")) sum noprint;
  var x_n;
  output out=tot88 sum=tot88;
run;
proc means data=levels(where=(idvar="1998")) sum noprint;
  var x_n;
  output out=tot98 sum=tot98;
run;


**COMBINE 1988 AND 1998 TOTAL EXPORTS**;
proc sort data=tot88; by sic; run;
proc sort data=tot98; by sic; run;
data combine(drop=_type_ _freq_);
  merge tot88 tot98;
  change = (((tot98/tot88)**.1)-1)*100;
run;

**PRINT OUT RESULTS IN TABLE FORMAT**;
proc print data=combine noobs;
  var change;
  format change 5.1;
  title "TABLE 3";
  title2 "Export Growth by Industry 1988-1998";
  title3 "Compound Annual Rate (percent)";
  title4 "All Industries";
run;



***----------------------------------------------------------------------------------------------------------------***;
***                                EXPORT SIMILARITY INDEX - BY INDUSTRY AND FOREIGN DESTINATION                   ***;
***                                             POLLARD/COUGHLIN TABLES 4 AND 6                                    ***;
***----------------------------------------------------------------------------------------------------------------***;
libname in 'd:\sas\exportsgsp_88to98';
options linesize=122 pagesize=67 nocenter nodate;


data nomin88(keep=state sic x region);
  set in.x_n(where=(idvar in("1988")));
  **CREATE REGIONS OF WORLD**;
   if country=1220 then region="Canada";
   if country=5880 then region="Japan";
   if country=2010 then region="Mexico";
   if 1000<=country<=3999 and country not in(1220, 2010) then region="W.Hem";
   if 4000<=country<=4999 then region="Europe";
   if 5000<=country<=5999 and country not in (5880) then region="Asia";
   if 6000<=country<=6999 then region="Oceania";
   if 7000<=country<=7999 then region="Africa";
  rename x_n=x;
run;

data nomin98(keep=state sic x region);
  set in.x_n(where=(idvar in("1998")));
  **CREATE REGIONS OF WORLD**;
   if country=1220 then region="Canada";
   if country=5880 then region="Japan";
   if country=2010 then region="Mexico";
   if 1000<=country<=3999 and country not in(1220, 2010) then region="W.Hem";
   if 4000<=country<=4999 then region="Europe";
   if 5000<=country<=5999 and country not in (5880) then region="Asia";
   if 6000<=country<=6999 then region="Oceania";
   if 7000<=country<=7999 then region="Africa";
  rename x_n=x;
run;


%macro years(i=);
data nomin; set nomin&i; run;

**-------------**;
**-BY INDUSTRY-**;
**-------------**;

**CREATE US EXPORTS BY SIC**;
proc sort data=nomin; by sic; run;
proc means data=nomin noprint;
  var x;
  by sic;
  output out=us_sic sum=us_sic;
run;


**CREATE TOTAL US EXPORTS**;
proc means data=nomin noprint;
  var x;
  output out=us_tot sum=us_tot;
run;


**CREATE US SIC SHARE OF US TOTAL EXPORTS**;
data nation(keep=sic nshare);
  if _n_=1 then set us_tot;
  set us_sic;
  nshare = us_sic/us_tot;
run;


**CREATE EXPORTS BY SIC AND STATE**;
proc sort data=nomin; by sic state; run;
proc means data=nomin noprint;
  var x;
  by sic state;
  output out=st_sic sum=st_sic;
run;


**CREATE TOTAL EXPORTS BY STATE**;
proc sort data=nomin; by state; run;
proc means data=nomin noprint;
  var x;
  by state;
  output out=st_tot sum=st_tot;
run;


**CREATE STATE SIC SHARE OF STATE TOTAL EXPORTS**;
proc sort data=st_sic; by state; run;
proc sort data=st_tot; by state; run;
data state(keep=sic state sshare);
  merge st_sic st_tot;
  by state;
  sshare = st_sic/st_tot;
run;


**COMBINE US AND STATE SHARES AND FIND MINIMUM BY SIC**;
proc sort data=nation; by sic; run;
proc sort data=state; by sic; run;
data index;
  merge nation state;
  by sic;
  minshare = (min(nshare, sshare))*100;
run;


**SUM MINIMUMS FROM LAST DATA STEP**;
proc sort data=index; by state; run;
proc means data=index noprint sum;
  var minshare;
  by state;
  output out=esii&i sum=esi&i;
run;



**-------------**;
**-BY REGION  -**;
**-------------**;

**CREATE US EXPORTS BY FOREIGN DESTINATION**;
proc sort data=nomin; by region; run;
proc means data=nomin noprint;
  var x;
  by region;
  output out=us_reg sum=us_reg;
run;


**CREATE TOTAL US EXPORTS**;
proc means data=nomin noprint;
  var x;
  output out=us_tot sum=us_tot;
run;


**US FOREIGN DESTINATION SHARE OF US TOTAL EXPORTS**;
data nation(keep=region nshare);
  if _n_=1 then set us_tot;
  set us_reg(where=(region<>""));
  nshare = us_reg/us_tot;
run;


**CREATE EXPORTS BY FOREIGN DESTINATION AND STATE**;
proc sort data=nomin; by region state; run;
proc means data=nomin noprint;
  var x;
  by region state;
  output out=st_reg sum=st_reg;
run;


**CREATE EXPORTS BY STATE**;
proc sort data=nomin; by state; run;
proc means data=nomin noprint;
  var x;
  by state;
  output out=st_tot sum=st_tot;
run;


**CREATE STATE F.D. AS SHARE OF STATE TOTAL EXPORTS**;
proc sort data=st_reg; by state; run;
proc sort data=st_tot; by state; run;
data state(keep=region state sshare);
  merge st_reg st_tot;
  by state;
  sshare = st_reg/st_tot;
  if region="" then delete;
run;


**COMBINE STATE AND US SHARES BY F.D. AND FIND MINIMUM**;
proc sort data=nation; by region; run;
proc sort data=state; by region; run;
data index;
  merge nation state;
  by region;
  minshare = (min(nshare, sshare))*100;
run;


**SUM MINIMUMS FROM LAST DATA STEP**;
proc sort data=index; by state; run;
proc means data=index noprint sum;
  var minshare;
  by state;
  output out=esir&i sum=esi&i;
run;


%mend;
%years(i=88);
%years(i=98);



*------------------*;
*-- BY INDUSTRY  --*;
*------------------*;
data ind(drop=state);
  merge esii88 esii98;
  by state;
  staten = stnamel(state);
  diff = esi98-esi88;
run;


**PRINT OUT RESULTS IN TABLE FORMAT**;
proc sort data=ind; by staten; run;
proc print data=ind noobs;
  var staten  esi88 esi98 diff;
  format esi88 esi98 diff 5.1;
  title "TABLE 4";
  title2 "Export-Similarity Index on Industry Basis";
run;

proc print data=ind(where=(diff>=5)) noobs;
  var staten diff;
  title "TABLE 4 - Col. 3, Bold";
run;
proc print data=ind(where=(diff<=-5)) noobs;
  var staten diff;
  title "TABLE 4 - Col. 3, Italics";
run;
proc print data=ind(where=(esi88>80)) noobs;
  var staten esi88;
  title "TABLE 4 - Greater than 80";
run;
proc print data=ind(where=(diff>0)) noobs;
  var staten diff;
  title "TABLE 4 - Increases";
run;
proc print data=ind(where=(diff>=10)) noobs;
  var staten diff;
  title "TABLE 4 - Increases > 10%";
run;

*------------------*;
*--BY DESTINATION--*;
*------------------*;
data reg;
  merge esir88 esir98;
  by state;
  staten = stnamel(state);
  diff = esi98-esi88;
run;


**PRINT OUT RESULTS IN TABLE FORMAT**;
proc sort data=reg; by staten; run;
proc print data=reg noobs;
  var staten  esi88 esi98 diff;
  format esi88 esi98 diff 5.1;
  title "TABLE 6";
  title2 "Export-Similarity Index on Destination Basis";
run;

proc print data=reg(where=(diff>=5)) noobs;
  var staten diff;
  title "TABLE 6 - Col. 3, Bold";
run;
proc print data=reg(where=(diff<=-5)) noobs;
  var staten diff;
  title "TABLE 6 - Col. 3, Ital";
run;

proc print data=reg(where=(80<=esi88<=90)) noobs;
  var staten esi88;
  title "TABLE 6 - Between 80 and 90";
run;
proc print data=reg(where=(esi88>90)) noobs;
  var staten esi88;
  title "TABLE 6 - Greater than 90";
run;
proc print data=reg(where=(-5<=diff<=5)) noobs;
  var staten diff;
  title "TABLE 6 - Between -5 and 5";
run;



***----------------------------------------------------------------------------------------------------------------***;
***                                     COMPOUND ANNUAL EXPORT GROWTH BY FOREIGN DESTINATION                       ***;
***                                             POLLARD/COUGHLIN TABLE 5                                           ***;
***----------------------------------------------------------------------------------------------------------------***;
libname in 'd:\sas\exportsgsp_88to98';
options nocenter nodate nonumber linesize=100 pagesize=100;


**SHRINK EXPORT DATA SET -- KEEP ONLY 1988 AND 1998**;
data levels(drop=country);
  length region $40.;
  set in.x_n(where=(idvar in("1988","1998")));
  **PICK OUT REGIONS OF THE WORLD**;
    if country=1220 then region="Canada";
    if country=5880 then region="Japan";
    if country=2010 then region="Mexico";
    if 1000<=country<=3999 and country not in(1220, 2010) then region="Rest of West";
    if 4000<=country<=4999 then region="Europe";
    if 5000<=country<=5999 and country not in (5880) then region="Asia";
    if 6000<=country<=6999 then region="Oceania";
    if 7000<=country<=7999 then region="Africa";
    if country>=8000 then delete;
run;


*---------------------------*;
*---BY FOREIGN DESTINATION--*;
*---------------------------*;
**CREATE EXPORTS BY YEAR AND FOREIGN DESTINATION**;
proc sort data=levels; by region; run;
proc means data=levels(where=(idvar="1988")) sum noprint;
  var x_n;
  by region;
  output out=x88 sum=x88;
run;
proc means data=levels(where=(idvar="1998")) sum noprint;
  var x_n;
  by region;
  output out=x98 sum=x98;
run;


**COMBINE 1988 AND 1998 EXPORTS BY F.D. DATA**;
proc sort data=x88; by region; run;
proc sort data=x98; by region; run;
data combine(drop=_type_ _freq_);
  merge x88 x98;
  by region;
  **COMPOUND ANNUAL CHANGE**;
    change = (((x98/x88)**.1)-1)*100;
run;


**PRINT OUT RESULTS IN TABLE FORMAT**;
proc print data=combine noobs;
  var region change;
  format change 4.1;
  title "TABLE 5";
  title2 "Manufacturing Export Growth";
  title3 "by Foreign Market";
  title4 "1988-1998";
  title5 "Compound Annual Growth";
run;



*---------------------------*;
*---       TOTAL          --*;
*---------------------------*;
**CREATE EXPORTS BY YEAR**;
proc means data=levels(where=(idvar="1988")) sum noprint;
  var x_n;
  output out=tot88 sum=tot88;
run;
proc means data=levels(where=(idvar="1998")) sum noprint;
  var x_n;
  output out=tot98 sum=tot98;
run;


**COMBINE 1988 AND 1998 TOTAL EXPORTS DATA**;
data combine(drop=_type_ _freq_);
  merge tot88 tot98;
  change = (((tot98/tot88)**.1)-1)*100;
run;


**PRINT OUT RESULTS IN TABLE FORMAT**;
proc print data=combine noobs;
  var change;
  format change 4.1;
  title "TABLE 5";
  title2 "Manufacturing Export Growth";
  title3 "by Foreign Market";
  title4 "1988-1998";
  title5 "Compound Annual Growth";
  title6 "World";
run;



***----------------------------------------------------------------------------------------------------------------***;
***                                        CLASSIC SHIFT SHARE RESULTS                                             ***;
***                                        POLLARD/COUGHLIN TABLE 7                                                ***;
***----------------------------------------------------------------------------------------------------------------***;
libname in 'd:\sas\exportsgsp_88to98';
options linesize=122 pagesize=67 nocenter nodate;

data x88(keep=state sic x88 country region);
  set in.x_n(where=(idvar in("1988")));

  **CREATE REGIONS OF WORLD**;
  if country=1220 then region="Canada";
  if country=5880 then region="Japan";
  if country=2010 then region="Mexico";
  if 1000<=country<=3999 and country not in(1220, 2010) then region="W.Hem";
  if 4000<=country<=4999 then region="Europe";
  if 5000<=country<=5999 and country not in (5880) then region="Asia";
  if 6000<=country<=6999 then region="Oceania";
  if 7000<=country<=7999 then region="Africa";

  rename x_n=x88;
run;

data x98(keep=state sic x98 country region);
  set in.x_n(where=(idvar in("1998")));

  **CREATE REGIONS OF WORLD**;
  if country=1220 then region="Canada";
  if country=5880 then region="Japan";
  if country=2010 then region="Mexico";
  if 1000<=country<=3999 and country not in(1220, 2010) then region="W.Hem";
  if 4000<=country<=4999 then region="Europe";
  if 5000<=country<=5999 and country not in (5880) then region="Asia";
  if 6000<=country<=6999 then region="Oceania";
  if 7000<=country<=7999 then region="Africa";

  rename x_n=x98;
run;



**EXPORTS BY STATE BY INDUSTRY**;
proc sort data=x88; by state sic; run;
proc means data=x88 sum noprint;
  var x88;
  by state sic;
  output out=Xis0 sum=Xis0;
run;

proc sort data=x98; by state sic; run;
proc means data=x98 sum noprint;
  var x98;
  by state sic;
  output out=Xis1 sum=Xis1;
run;

**GROWTH OF STATE EXPORTS BY INDUSTRY**;
data sigro;
  merge Xis0 Xis1;
  by state sic;
  xis=((Xis1/Xis0)-1);
run;
proc sort data=sigro; by sic state; run;



**EXPORTS FOR NATION BY INDUSTRY**;
proc sort data=x88; by sic; run;
proc means data=x88 sum noprint;
  var x88;
  by sic;
  output out=ni88 sum=ni88;
run;

proc sort data=x98; by sic; run;
proc means data=x98 sum noprint;
  var x98;
  by sic;
  output out=ni98 sum=ni98;
run;

**GROWTH OF NATION EXPORTS BY INDUSTRY**;
data nigro;
  merge ni88 ni98;
  by sic;
  xin=((ni98/ni88)-1);
run;



**EXPORTS BY NATION**;
proc means data=x88 sum noprint;
  var x88;
  output out=e88 sum=e88;
run;
proc means data=x98 sum noprint;
  var x98;
  output out=e98 sum=e98;
run;

**GROWTH OF NATION EXPORTS**;
data ngro;
  merge e88 e98;
  xn=((e98/e88)-1);
run;



**COMBINE NATION GROWTH AND NATION BY INDUSTRY GROWTH**;
data n;
  if _n_=1 then set ngro;
  set nigro;
run;


**COMBINE STATE BY INDUSTRY AND NATION DATA SETS**;
data all;
  merge sigro n;
  by sic;

  **CALCULATE SHARE COMPONENTS**;
  ime=Xis0*(xin-xn);
  ge=Xis0*(xis-xin);

  growth = Xis1-Xis0;
  ne=xn*Xis0;
  nrc=growth-ne;
run;



**COMBINE ABOVE VALUES BY STATE**;
proc sort data=all; by state; run;
proc means data=all sum noprint;
  var nrc ime ge Xis1;
  by state;
  output out=classic sum=;
run;



**"NORMALIZE"  THE SHARE COMPONENTS**;
data classic(drop=state);
  set classic;
  nrc_p = (nrc/(Xis1-nrc))*100;
  ime_p = (ime/(Xis1-nrc))*100;
  ge_p = (ge/(Xis1-nrc))*100;
  staten = stnamel(state);
run;



**PRINT OUT RESULTS IN TABLE FORMAT**;
proc sort data=classic; by staten; run;
proc print data=classic noobs;
  var  staten nrc_p ime_p ge_p;
  format nrc_p ime_p ge_p 6.1;
  title "TABLE 7";
  title2 "Classic Shift-Share Results - Nominal";
run;

proc print data=classic(where=(nrc_p>=30)) noobs;
  var staten nrc_p;
  title "TABLE 7 - Col. 1, Bold";
run;

proc print data=classic(where=(nrc_p<=-30)) noobs;
  var staten nrc_p;
  title "TABLE 7 - Col. 1, Italics";
run;



***----------------------------------------------------------------------------------------------------------------***;
***                                        CLASSIC SHIFT SHARE RESULTS                                             ***;
***                                        POLLARD/COUGHLIN TABLE 8                                                ***;
***----------------------------------------------------------------------------------------------------------------***;
filename in2 'd:\sas\state_exports\rawdata\SIC_2digit';
options linesize=250 pagesize=81 nocenter nodate;

%let start = 88;
%let end = 98;

data x88(keep=state sic x88 country region);
  length region $20.;
  infile in2(st88om) firstobs=1;
  input @1 state $2. @3 sic 2. @5 country 4.  x88  12. junk1-junk6;

  **KEEP MANUFACTURING INDUSTRIES**;
    if 20<=sic<=39;
  **KEEP 50 STATES and DC**;
    if state in("PR","VI") then delete;
  **CREATE REGIONS**;
    if country=1220 then region="Canada";
    if country=5880 then region="Japan";
    if country=2010 then region="Mexico";
    if 1000<=country<=3999 and country not in(1220, 2010) then region="Rest of West";
    if 4000<=country<=4999 then region="Europe";
    if 5000<=country<=5999 and country not in (5880) then region="Asia"; 
    if 6000<=country<=6999 then region="Oceania";
    if 7000<=country<=7999 then region="Africa";
run;


data x98(keep=state sic x98 country region);
  length region $20.;
  infile in2(st98om) firstobs=1;
  input @2 state $2. @7 sic 2.  @12 country 4.  @18 x98;

  **KEEP MANUFACTURING INDUSTRIES**;
    if 20<=sic<=39;
  **KEEP 50 STATES and DC**;
    if state in("PR","VI") then delete;
  **CREATE REGIONS**;
    if country=1220 then region="Canada";
    if country=5880 then region="Japan";
    if country=2010 then region="Mexico";
    if 1000<=country<=3999 and country not in(1220, 2010) then region="Rest of West";
    if 4000<=country<=4999 then region="Europe";
    if 5000<=country<=5999 and country not in (5880) then region="Asia"; 
    if 6000<=country<=6999 then region="Oceania";
    if 7000<=country<=7999 then region="Africa";
run;



**************;
**STATE DATA**;
**************;
*------------------------------*;
**TOTAL EXPORT LEVELS BY STATE**;
*------------------------------*;
  **Beginning Year**;
    proc sort data=x&start; by state sic; run;
    proc means data=x&start sum noprint;
      var x&start;
      by state;
      output out=xs&start sum=xs&start;
    run;
  **Ending Year**;
    proc sort data=x&end; by state sic; run; 
    proc means data=x&end sum noprint;
      var x&end;
      by state;
      output out=xs&end sum=xs&end;
    run;

**TOTAL EXPORT GROWTH BY STATE**;
  data xs(drop=xs&start xs&end);
    merge xs&start xs&end;
    xs=(xs&end/xs&start)-1;
  run;



*------------------------------*;
**EXPORTS BY STATE BY INDUSTRY**;
*------------------------------*;
  **Beginning Year**;
    proc sort data=x&start; by sic state; run;
    proc means data=x&start sum noprint;
      var x&start;
      by sic state;
      output out=xis&start sum=xis&start;
    run;
    proc sort data=xis&start; by sic state; run;
  **Ending Year**;
    proc sort data=x&end; by sic state; run;
    proc means data=x&end sum noprint;
      var x&end;
      by sic state;
      output out=xis&end sum=xis&end;
    run;
    proc sort data=xis&end; by sic state; run;

**STATE - BY INDUSTRY**;
  data xis;
    merge xis&start xis&end;
    by sic state;
  run;


*----------------------------*;
**EXPORTS BY STATE BY REGION**;
*----------------------------*;
  **Beginning Year**;
    proc sort data=x&start; by region state; run;
    proc means data=x&start sum noprint;
      var x&start;
      by region state;
      output out=xjs&start sum=xjs&start;
    run;
    proc sort data=xjs&start; by region state; run;
  **Ending Year**;
    proc sort data=x&end; by region state; run;
    proc means data=x&end sum noprint;
      var x&end;
      by region state;
      output out=xjs&end sum=xjs&end;
    run;
    proc sort data=xjs&end; by region state; run;

**STATE - BY REGION**;
  data xjs;
    merge xjs&start xjs&end;
    by region state;
  run;




**************;
**  US DATA **;
**************;
*----------------------------*;
**TOTAL EXPORT LEVELS FOR US**;
*----------------------------*;
  **Beginnning year**;
    proc means data=x&start sum noprint;
      var x&start;
      output out=xn&start sum=xn&start;
    run;
  **Ending year**;
    proc means data=x&end sum noprint;
      var x&end;
      output out=xn&end sum=xn&end;
    run;

**TOTAL EXPORT GROWTH FOR US**;
  data xn(drop=xn&start xn&end);
    merge xn&start xn&end;
    xn=(xn&end/xn&start)-1;
  run;


*----------------------------*;
**EXPORTS BY INDUSTRY FOR US**;
*----------------------------*;
  **Beginnning year**;
    proc sort data=x&start; by sic; run;
    proc means data=x&start sum noprint;
      var x&start;
      by sic;
      output out=xin&start sum=xin&start;
    run;
    proc sort data=xin&start; by sic; run;
  **Ending year**;
    proc sort data=x&end; by sic; run;
    proc means data=x&end sum noprint;
      var x&end;
      by sic;
      output out=xin&end sum=xin&end;
    run;
    proc sort data=xin&end; by sic; run;

**NATION - BY INDUSTRY**;
  data xin;
    merge xin&start xin&end;
    by sic;
  run;



*--------------------------*;
**EXPORTS BY REGION FOR US**;
*--------------------------*;
  **Beginnning year**;
    proc sort data=x&start; by region; run;
    proc means data=x&start sum noprint;
      var x&start;
      by region;
      output out=xjn&start sum=xjn&start;
    run;
    proc sort data=xjn&start; by region; run;
  **Ending year**;
    proc sort data=x&end; by region; run;
    proc means data=x&end sum noprint;
      var x&end;
      by region;
      output out=xjn&end sum=xjn&end;
    run; 
    proc sort data=xjn&end; by region; run;

**NATION - BY REGION**;
  data xjn;
    merge xjn&start xjn&end;
    by region;
  run;




***************************************;
**STATE SHARE AND SECTORAL COMPONENTS**;
***************************************;
  data industry(drop=_type_ _freq_);
    if _n_=1 then set xn;
    merge xis xin;
    by sic;
    **Growth of industry in nation;
      xin=(xin&end/xin&start)-1;

    **Growth of industry in industry;
      xis=(xis&end/xis&start)-1;

    **State share;
      statshar = xis&start*(xn);

    **Sectoral shift;
      ime = xis&start*((xin)-(xn));

    **Part 1 of Competitive Effect;
      ce1=xis&start*(xis-xin);
  run;

  proc sort data=industry; by state; run;

  *-----------------------------------*;
  **SUM ALL INDUSTRIES FOR EACH STATE**;
  *-----------------------------------*;
    proc sort data=industry; by state; run;
    proc means data=industry sum noprint;
      var statshar ime ce1;
      by state;
      output out=ind_stat sum=;
    run;





***************************************;
**COUNTRY SHIFT                      **;
***************************************;
  data country(drop=_type_ _freq_);
    if _n_=1 then set xn;
    merge xjs xjn;
    by region;
    **Growth of exports for each region for the nation**;
      xjn=(xjn&end/xjn&start)-1;

    **Country shift**;
      ge = xjs&start*((xjn)-(xn));

    **Part 2 of Competitive Effect;
      ce2=ge;
  run;

  proc sort data=country; by state; run;

  *----------------------------------*;
  **SUM ALL COUNTRIES FOR EACH STATE**;
  *----------------------------------*;
    proc sort data=country; by state; run;
    proc means data=country sum noprint;
      var ge ce2;
      by state;
      output out=cty_stat sum=;
    run;




***************************************;
**NET RELATIVE CHANGE                **;
***************************************;
  data nrc(keep=state gro xs&end);
    if _n_=1 then set xn;
    merge xs&start xs&end;
    by state;

    **Growth in state total exports;
      xs=(xs&end/xs&start)-1;

    **Net relative change;
      nrc=(xs&start*xs)-(xs&start*(xn));
      gro = xs&end-xs&start;
run;




**************************;
**COMBINE ALL COMPONENTS**;
**************************;
data all(drop=_type_ _freq_ state);
  merge ind_stat cty_stat nrc;
  by state;

  **Using the definition in paper;
    ce_ae = ce1-ce2;
    nrc = gro-statshar;

  **Normalization of Results**;
  **  Percentage of export level that would have been achieved **;
  **    if the state exports had grown at the national level   **;
    nrc_p = (nrc/(xs&end-nrc))*100;
    ime_p = (ime/(xs&end-nrc))*100;
    ge_p = (ge/(xs&end-nrc))*100;
    ceae_p = (ce_ae/(xs&end-nrc))*100;

  staten = stnamel(state);
run;


proc sort data=all; by staten; run;
proc print data=all noobs;
  var staten nrc_p ime_p ceae_p ge_p;
  format nrc_p ime_p ceae_p ge_p 6.1;
  title "TABLE 8";
  title2 "Gazel-Schwer Shift-Share Results-Nominal";
run;


******************************************;
**FIND THE LARGEST EFFECT FOR EACH STATE**;
******************************************;
data text;
  set all;
  ceae_p=abs(ceae_p);
  ime_p=abs(ime_p);
  ge_p=abs(ge_p);

     if ceae_p>ge_p and ceae_p>ime_p then ceaer=1;
     if ime_p>ge_p and ime_p>ceae_p then imer=1;
     if ge_p>ime_p and ge_p>ceae_p then ger=1;

     if ime_p<ge_p and ime_p<ceae_p then imer=3;
     if ge_p<ime_p and ge_p<ceae_p then ger=3;

     if ceae_p<ime_p<ge_p or ceae_p>ime_p>ge_p then imer=2;
     if ceae_p<ge_p<ime_p or ceae_p>ge_p>ime_p then ger=2;
run;

proc print data=text noobs;
  var staten imer ceaer ger;
  title "TABLE 8 - Bold Placement";
run;