Presentation Transcript

1. Import NAICS OCEAN County GDP Wage New 3 Import Census Zips Import 2 CSV SQL1 Import 1 DRIVER County QCEW Public Data Final Sums New 1
2. SQL 1 Create Macro VARS for Program – to Run data based on Value of Parameter during Execution AREA_NALL1 – AREA_NALL30 NONP1 – NONP4 NONPFIPS1 – NONPFIPS30 STATESALL1 – STATESALL30 STATEFIPSALL1 – STATEFIPSALL30
3. FIPS SAS DATASET CONTENTS: AREA_N = 5 BYTES USED AS AREA, FIPS STATES = 2 BYTES USED AS STATE ABREVIATION STATEFIPS_A = 2 BYTES STATE IDENTIFIER IN FIPS When ITERATION = 1 then the following will be resolved in the macro variables: AREA_NALL1 = 02000 STATESALL1 = AK STATEFIPSALL1 = 02 This is because these are the values in the FIRST ROW of the SAS Dataset. When ITERATION = 2 then the following will be resolved in the macro variables: AREA_NALL1 = 01000 STATESALL1 = AL STATEFIPSALL1 = 01 This is because these are the values in the SECOND ROW of the SAS Dataset. NOTE1: Macro Variable : &OFIPS contains 30 2 byte Numeric FIPS CODE for States in Ocean Economy
4. DO NOT Use Industry Code N Industry Code and Ocean Indicator File Use Industry Code Y This data contains INDUSTRY CODE and Y N Indicator of whether CODE is OCEAN Y, or Not N. INDUSTRY CODE data is located in TABLES Directory with name: INDUSTRY_CODES.xls. SAS Dataset: INDUSTRY_CODES is located in SASDatasetsInputs directory. *If a change needs to be made to the table ensure to update the SAS Dataset. *NOTE2: Macro Variable &OCN_CDS contains list of all Industry Codes with Y
5. Industry Code and Y N Indicator if Used in Ocean Economy Report 4 Y 5 Y 7 Y 8 Y 9 Y 10 N 11 Y 14 N 15 N 16 N 17 N 18 N 19 Y 20 N 21 N 22 Y 23 N 24 Y 26 Y 27 N 28 N 29 N 30 N 31 N 32 N 33 N 34 N 35 Y 37 N 38 N 39 Y 40 N 41 N 42 N 43 Y 44 Y 45 N 46 N 47 N 48 N 49 N 50 N 51 N 52 N 53 N 54 N 56 N 57 N 59 N 60 N 61 Y 62 N 64 N 65 N 66 N 68 N 69 N 70 N 71 Y 75 Y 76 Y 77 N 78 N
6. 22 10 Utilities 23 11 Construction 42 34 Wholesale 44 35 Retail 45 35 Retail 55 62 Management 61 66 Education 81 77 Other Services 78 Government 311 NA 26 Food 312 NA 26 Food 313 NA 27 Textiles 314 NA 27 Textiles 315 NA 28 Apparel 316 NA 28 Apparel 322 NA 29 Paper 323 NA 30 Printing 324 NA 31 Petroleum Mfg 325 NA 32 Chemicals 326 NA 33 Plastics 481 NA 37 Air Transport 482 NA 38 Rail Transport 483 NA 39 Water Transport 484 NA 40 Truck Transport 485 NA 41 Transit 486 NA 42 Pipelines 487 NA 43 Other Transport 488 NA 43 Other Transport 492 NA 43 Other Transport 493 NA 44 Warehousing 511 NA 46 Warehousing 512 NA 47 Motion Pictures 515 NA 48 Broadcasting and Telecom 517 NA 48 Broadcasting and Telecom 518 NA 49 Info and Data Processing 519 NA 49 Info and Data Processing 521 NA 51 Federal Reserve 522 NA 51 Federal Reserve 523 NA 52 Securities 524 NA 53 Insurance 525 NA 54 Funds and Trusts 531 NA 56 Real Estate 532 NA 57 Rental and Leasing 533 NA 57 Rental and Leasing 111 4 Crop and Animal 112 4 Crop and Animal 113 5 Forestry Fishing 114 5 Forestry Fishing 115 5 Forestry Fishing 211 7 Oil and Gas 212 8 Mining 213 9 Mining Support 321 14 Wood Products 327 15 Nonmetallic Mfg 331 16 Primary Metals 332 17 Fabricated Metals 333 18 Machinery 334 19 Computer and Electronics 335 20 Electrical 337 23 Furniture 339 24 MiscMfg 2 and 3 Digit NAICS Codes and GDP CODE Mapping.
7. 3 and 4 Digit NAICS Codes and GDP CODE Mapping. 561 64 Admin and Support 562 65 Waste Management 621 68 Amulatory Health 622 69 Hospitals and Nursing 623 69 Hospitals and Nursing 624 70 Social Assistance 711 71 Arts and Museums 712 71 Arts and Museums 713 71 Amusements Gambling Recreation 721 75 Accomodations 722 76 Food Services 3361 21 Motor Vehicle 3362 21 Motor Vehicle 3363 21 Motor Vehicle 3364 22 Other Trans Equip 3365 22 Other Trans Equip 3366 22 Other Trans Equip 3369 22 Other Trans Equip 5411 59 Legal 5415 60 Computer Systems Design 5412 61 Other Prof Sci and Tech Services 5413 61 Other Prof Sci and Tech Services 5414 61 Other Prof Sci and Tech Services 5416 61 Other Prof Sci and Tech Services 5417 61 Other Prof Sci and Tech Services 5418 61 Other Prof Sci and Tech Services 5419 61 Other Prof Sci and Tech Services
8. Data from NAICSOCEAN.CSV file Located in TABLES Directory Ocean Indicator Ocean Sector NAICS Code OCEAN_SCODE OCEAN_ICODE NOEP_ICODE NOEP_SCODE Marine Related Construction Construction 237990 CO01 CO00 10 11 Total Construction 999999 CO00 CO00 10 99 Fish Hatcheries & Aquaculture Living Resources 112511 LR04 LR00 20 22 Fish Hatcheries & Aquaculture Living Resources 112512 LR04 LR00 20 22 Fish Hatcheries & Aquaculture Living Resources 112519 LR04 LR00 20 22 Fishing Living Resources 114111 LR03 LR00 20 21 Fishing Living Resources 114112 LR03 LR00 20 21 Fishing Living Resources 114119 LR03 LR00 20 21 Total Living Resources 999999 LR00 LR00 20 99 Seafood Processing Living Resources 311711 LR01 LR00 20 23 Seafood Processing Living Resources 311712 LR01 LR00 20 23 Seafood Markets Living Resources 445220 LR05 LR00 20 24 Oil & Gas Exploration Minerals 211111 MI02 MI00 30 32 Oil & Gas Exploration Minerals 211112 MI02 MI00 30 32 Limestone, Sand & Gravel Minerals 212321 MI04 MI00 30 31 Limestone, Sand & Gravel Minerals 212322 MI04 MI00 30 31 Oil & Gas Exploration Minerals 213111 MI02 MI00 30 32 Oil & Gas Exploration Minerals 213112 MI02 MI00 30 32 Total Minerals 999999 MI00 MI00 30 99 Oil & Gas Exploration Minerals 541360 MI02 MI00 30 32 Total Ocean Economy 999999 AL00 AL00 90 99
9. Scenic Water Tours Tourism & Recreation 487210 TO09 TO00 50 56 Amusement & Recreation Services Tourism & Recreation 487990 TO08 TO00 50 58 Amusement & Recreation Services Tourism & Recreation 532292 TO08 TO00 50 58 Amusement & Recreation Services Tourism & Recreation 611620 TO08 TO00 50 58 Zoos & Aquaria Tourism & Recreation 712130 TO10 TO00 50 59 Zoos & Aquaria Tourism & Recreation 712190 TO10 TO00 50 59 Marinas Tourism & Recreation 713930 TO02 TO00 50 55 Amusement & Recreation Services Tourism & Recreation 713990 TO08 TO00 50 58 Hotels & Lodging Places Tourism & Recreation 721110 TO06 TO00 50 53 Hotels & Lodging Places Tourism & Recreation 721191 TO06 TO00 50 53 RV Parks & Campgrounds Tourism & Recreation 721211 TO07 TO00 50 54 Eating & Drinking Places Tourism & Recreation 722110 TO04 TO00 50 52 Eating & Drinking Places Tourism & Recreation 722211 TO04 TO00 50 52 Eating & Drinking Places Tourism & Recreation 722212 TO04 TO00 50 52 Eating & Drinking Places Tourism & Recreation 722213 TO04 TO00 50 52 Warehousing Transportation 4931 TR01 TR00 60 65 Search & Navigation Equipment Transportation 334511 TR03 TR00 60 64 Marine Freight Transportation 483111 TR02 TR00 60 61 Marine Passenger Transportation Transportation 483112 TR04 TR00 60 62 Marine Freight Transportation 483113 TR02 TR00 60 61 Marine Passenger Transportation Transportation 483114 TR04 TR00 60 62 Marine Transportation Services Transportation 488310 TR07 TR00 60 63 Marine Transportation Services Transportation 488320 TR07 TR00 60 63 Marine Transportation Services Transportation 488330 TR07 TR00 60 63 Marine Transportation Services Transportation 488390 TR07 TR00 60 63 Total Transportation 999999 TR00 TR00 60 99 Total Ocean Economy Total 999999 AL00 AL00 90 99
10. NAICSOcean.csv INFILE STATEMENT IN SAS PROGRAM IMPORT_NAICS_OCEAN OCEAN SAS Dataset Contents of OCEAN Dataset Sorted by NAICS: Contains: OCEANIND OCEANSECT NAICS NOEP_ICODE NOEP_SCODEOCEAN_ICODE OCEAN_SCODE Read Data from CSV into SAS Dataset Note 3 Macro Variable &ONAICS created here is list of NAICS codes used in Ocean Economy Reporting.
11. ALLSTA09.csv INFILE STATEMENT IN SAS PROGRAM IMPORT1 IF FIPS IN OCEAN FIPS LIST (&OFIPS) * See NOTE1 in SLIDE 3. Contents of STPUB.ALLSTATE09 Dataset STPUB.ALLSTATE09 Contains: AREA OWN NAICS YEAR AGGLVLANNSTAT ESTABS EMP WAGES FIPS_ST STATEFIPS FIPS STATE Read Data from CSV into SAS Dataset
12. GDP RGDP 09 ALL REVISED.CSV INFILE STATEMENT IN SAS PROGRAM IMPORT2_CSV IF FIPS IN OCEAN FIPS LIST (&OFIPS) * See NOTE1 in SLIDE 3. AND INDUSTRY_CODE IN (&OCN_CDS) * See NOTE2 in SLIDE 4. Contents of STPUB.GDP09_ALL Dataset STPUB.GDP09_ALL Contains: FIPS INDUSTRY_CODE GDP RGDP Read Data from CSV into SAS Dataset
13. ENOW_GEO_REFERENCE.CSV INFILE STATEMENT IN SAS PROGRAM STEP 1. COUNTY_GDP_WAGE_NEW3 IF FIPS IN OCEAN FIPS LIST (&FIPS) * See NOTE1 in SLIDE 3. ITERATES FROM 1 TO 30 SO FIRST ITERATION WILL BE FOR 02- (AK Alaska) Contents of ENOW Dataset Contains: FIPS COUNTY_NAME REGION STATE_FIPS COUNTY_FIPS CZCTY FEMA WATERSHED SHORE SHORE100 WAREHOUSE MINERALS URBANINFLUENCE NONP RURALURBAN ENOW Read Data from CSV into SAS Dataset Note 1. Macro Variable &OAREAS is list of FIPS per STATE – Based on which iteration, 1-30. So for 02 (AK) will be list of AREAS for OCEAN Economy in AK.
14. CN02AK09.CSV INFILE STATEMENT IN SAS PROGRAM STEP 2. COUNTY_GDP_WAGE_NEW3 IF AREA IN OCEAN AREA LIST (&OAREAS) * See Note 1 SLIDE 13. AND NAICS IN (&ONAICS) * See Note 3 in SLIDE 10. Contents of AK09_QCEWCTY Dataset Contains: AREA OWN NAICS YEAR AGGLVL ANNSTAT ESTABS EMP WAGES NAICS2 NAICS3 NAICS4 COUNTY_NAME STATE FIPS AK09_QCEWCTY Read Data from CSV into SAS Dataset
15. STEP 3. USE FORMATS TO MAP GDPCODE TO NAICS 2 3 AND 4 IN SAS PROGRAM COUNTY_GDP_WAGE_NEW3 NEW VARIABLE - GDPCODE AK09_QCEWCTY NAICS2 &GDP_A FORMAT NAICS3 &GDP_B FORMAT NAICS4 &GDP_C FORMAT
16. STEP 4. MERGE OCEAN ENOW AND AK09_QCEWCTY IN SAS PROGRAM COUNTY_GDP_WAGE_NEW3 Merge By NAICS AK09_QCEWCTY OCEAN Merge By FIPS ENOW AK09_CTYOCEAN
17. STEP 5. GET GDP RGDP AND WAGES TOTAL FOR STATE TOTAL COUNTY_GDP_WAGE_NEW3 STPUB.GDP09_ALL GDP_AK09 Where FIPS = 02 AK First Iteration STPUB.ALLSTATE09 Statewage_AK09 Statewage_AK09 Where AREA = 02000 AK First Iteration See Step 3 to Create GDP Code from NAICS 2 3 and 4 SUMMARY BY GDPCODE VAR WAGES
18. STEP 6. MERGE GDP_AK09 with STATEWAGE_AK09 IN SAS PROGRAM COUNTY_GDP_WAGE_NEW3 Merge By GDPCODE GDP_AK09 STATEWAGE_AK09 Contains: GDPCODE TOTGDP TOTRGDP TOTWAGES GDPWAGES_AK09
19. STEP 7. MERGE AK09_CTYOCEAN with GDPWAGES_AK09 IN SAS PROGRAM COUNTY_GDP_WAGE_NEW3 Merge By GDPCODE AK09_CTYOCEAN GDPWAGES_AK09 Contents of Data: AREA OWN NAICS YEAR AGGLVL ANNSTAT ESTABS EMP WAGES NAICS2 NAICS3 NAICS4 FIPS STATE COUNTY_NAME FLAG OCEANIND OCEANSECT NOEP_ICODE NOEP_SCODE OCEAN_ICODE OCEAN_SCODE REGION CZCTY FEMA WATERSHED SHORE SHORE100 WAREHOUSE MINERALS URBANINFLUENCE NONP RURALURBAN GDPCODE TOTGDP TOTRGDP TOTGAGES STATE AK09_CTY
20. SAS DATASETS: AK09_CTYOCEAN and CTYPUB.AK09_CTYOCEAN. SAME DATA IN BOTH. Contents of Data: AREA OWN NAICS YEAR AGGLVL ANNSTAT ESTABS EMP WAGES NAICS2 NAICS3 NAICS4 FIPS STATE COUNTY_NAME FLAG OCEANIND OCEANSECT NOEP_ICODE NOEP_SCODE OCEAN_ICODE OCEAN_SCODE REGION CZCTY FEMA WATERSHED SHORE SHORE100 WAREHOUSE MINERALS URBANINFLUENCE NONP RURALURBAN
21. SAS DATASETS: CTYPUB.AK09_CTY_DETAIL Contents of Data: FIPS STATE COUNTY_NAME REGION OCEANSECT OCEANIND OCEAN_SCODE NOEP_SCODE WAGES EMP ESTABS GDP RGDP FLAG CZCTY FEMA WATERSHED SHORE SHORE100 URBANINFLUENCE NONP RURALURBAN WAREHOUSE MINERALS SAS DATASETS: CTYPUB.AK09_CTY_DETAIL1 - where: If oceanind = “Warehousing” and shore = 0 then delete; If ocean_scode = 30 and Minerals =1 then delete; If ocean_scode = 50 and shore = 1 and shore100 = 0 and nonp= 0 then delete; Contents of Data: FIPS STATE COUNTY_NAME REGION OCEANSECT OCEANIND OCEAN_SCODE NOEP_SCODE WAGES EMP ESTABS GDP RGDP FLAG CZCTY FEMA WATERSHED SHORE SHORE100 URBANINFLUENCE NONP RURALURBAN WAREHOUSE MINERALS
22. STEP 8. SUMMARIZE AK09_CTY IN SAS PROGRAM COUNTY_GDP_WAGE_NEW3 AK09_CTY CTYPUB.AK09_CTY_DETAIL SUMMARIZE DATA