1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 72 73 PROC SQL; 74 CREATE TABLE BASE_JOINX AS 75 SELECT 76 A.ACCOUNT_NUM LENGTH=8 77 , A.OWNER_NAME FORMAT=$30. LENGTH=30 78 , CASE WHEN A.OWNER_NAME LIKE '%TRUST%' THEN 'TRUST' 79 WHEN A.OWNER_NAME LIKE '% LLC%' THEN 'LLC' 80 WHEN A.OWNER_NAME LIKE '% INC%' THEN 'INC' 81 WHEN A.OWNER_NAME LIKE '% LP%' THEN 'LP' 82 WHEN A.OWNER_NAME LIKE '% LTD%' THEN 'LTD' 83 WHEN A.OWNER_NAME LIKE '% BANK %' THEN 'BANK_OWNED' 84 WHEN (A.OWNER_NAME LIKE '%REAL ESTATE%' OR A.OWNER_NAME LIKE '%REAL EST%') THEN 'INC' 85 WHEN A.OWNER_NAME LIKE '% PROFESSIONALS%' THEN 'INC' 86 WHEN A.OWNER_NAME LIKE '% PROFESSIONAL%' THEN 'INC' 87 WHEN A.OWNER_NAME LIKE '% PARTNERS%' THEN 'INC' 88 WHEN A.OWNER_NAME LIKE '% PARTNER%' THEN 'INC' 89 WHEN A.OWNER_NAME LIKE '% PROPERTY%' THEN 'INC' 90 WHEN A.OWNER_NAME LIKE '% PROPERTIES%' THEN 'INC' 91 WHEN A.OWNER_NAME LIKE '% GROUP%' THEN 'INC' 92 WHEN A.OWNER_NAME LIKE '% INVESTMENTS%' THEN 'INC' 93 WHEN A.OWNER_NAME LIKE '% INVESTMENT%' THEN 'INC' 94 WHEN (A.OWNER_NAME LIKE '% ASSOCIATION%' OR A.OWNER_NAME LIKE '% ASSOC%') THEN 'ASSOC' 95 WHEN (A.OWNER_NAME LIKE '% CITY OF%' OR A.OWNER_NAME LIKE '% CITY%') THEN 'CITY' 96 WHEN (A.OWNER_NAME LIKE '% TOWN OF%' OR A.OWNER_NAME LIKE '% TOWN%') THEN 'TOWN' 97 WHEN (A.OWNER_NAME LIKE '% ESTATE %' OR A.OWNER_NAME LIKE '% EST %') THEN 'ESTATE' 98 ELSE 'OWNER' 99 END AS OWNER_TYPE FORMAT=$10. LENGTH=10 100 , CASE WHEN STRIP(A.OWNER_ST) NE 'TX' THEN 'OUT_OF_ST' 101 WHEN STRIP(A.OWNER_CITY) NE STRIP(B.CITY) THEN 'OUT_OF_CITY' 102 WHEN STRIP(A.OWNER_CITY) EQ STRIP(B.CITY) THEN 'LOCAL' 103 ELSE 'NOT_CLASSIFIED' 104 END AS OWNER_TYPE2 FORMAT=$14. LENGTH=14 105 , CASE WHEN STRIP(A.OWNER_ADDRESS) EQ STRIP(A.SITUS_ADDRESS) THEN 'MATCH' 106 WHEN SUBSTR(STRIP(A.OWNER_ADDRESS),1,7) EQ SUBSTR(STRIP(A.SITUS_ADDRESS),1,7) AND 106 ! SUBSTR(STRIP(A.OWNER_ADDRESS),8,22) NE SUBSTR(STRIP(A.SITUS_ADDRESS),8,22) THEN 'SUSPECT' 107 WHEN SUBSTR(STRIP(A.OWNER_ADDRESS),8,22) EQ SUBSTR(STRIP(A.SITUS_ADDRESS),8,22) AND 107 ! SUBSTR(STRIP(A.OWNER_ADDRESS),1,7) NE SUBSTR(STRIP(A.SITUS_ADDRESS),1,7) THEN 'SUSPECT' 108 WHEN STRIP(A.OWNER_ADDRESS) NE STRIP(A.SITUS_ADDRESS) THEN 'DIFF_ADDRSS' 109 END AS QLTY_TYPE FORMAT=$11. LENGTH=11 110 , A.PROP_TYPE 111 , A.OWNER_ADDRESS FORMAT=$30. LENGTH=30 112 , A.OWNER_CITYSTATE FORMAT=$30. LENGTH=30 113 , A.OWNER_CITY 114 , A.OWNER_ST 115 , A.OWNER_ZIP FORMAT=$5. LENGTH=5 116 , A.SITUS_ADDRESS AS PRPTY_ADRS FORMAT=$30. LENGTH=30 117 , B.STREET_NUM LENGTH=6 118 , B.Street_NameAS STREET_NAME FORMAT=$25. LENGTH=25 119 , B.STREET_TYPE FORMAT=$5. LENGTH=5 120 , A.LEGALDESCRIPTION AS LEGAL_DESC 121 , B.CITY AS PRPTY_CITY FORMAT=$10. LENGTH=10 122 , B.ZIPCODE AS PRPTY_ZIP FORMAT=$5. LENGTH=5 123 , CASE WHEN (MISSING(A.COUNTYC) OR A.COUNTYC NE '220') THEN A.COUNTYC 124 ELSE COUNTY1 125 END AS COUNTYFORMAT=$10. INFORMAT=$10. LENGTH=10 126 , A.SCHOOL1 AS ISD 127 , CASE WHEN STRIP(A.SWIMMING_POOL_IND) = 'X' THEN 'Y' 128 ELSE 'N' 129 END AS POOL_IND 130 , A.Land_Value AS LAND_VALUE FORMAT=DOLLAR12. 131 , A.Improvement_Value AS HOUSE_VALUE FORMAT=DOLLAR12. 132 , A.Total_Value AS TOT_VALUE FORMAT=DOLLAR12. INFORMAT=DOLLAR12. 133 , A.Num_Bedrooms AS BEDROOMS FORMAT=COMMA3. INFORMAT=COMMA3. LENGTH=3 134 , A.Num_Bathrooms AS BATHROOMS FORMAT=COMMA3. INFORMAT=COMMA3. LENGTH=3 135 , A.Year_Built AS YR_BUILT FORMAT=COMMA4. LENGTH=4 136 , A.Living_Area AS SQ_FT FORMAT=COMMA7. INFORMAT=COMMA7. LENGTH=7 137 , A.Land_Acres AS LOT FORMAT=COMMA9.2 INFORMAT=COMMA9.2 138 139 FROM REFINE A 140 141 LEFT OUTER JOIN TAD_LOCATION B 142 ON A.ACCOUNT_NUM = B.ACCOUNT_NUM 143 144 WHERE A.OWNER_NAME NOT IN ('FORT WORTH, CITY OF','CITY FORT WORTH') 145 AND A.OWNER_NAME NOT LIKE 'Current%Owner%' 146 AND NOT MISSING(A.OWNER_ADDRESS) 147 AND NOT MISSING(A.SITUS_ADDRESS) 148 AND ( 149 (A.Land_Value > 1000) 150 OR 151 (A.Improvement_Value > 1000) 152 OR 153 (A.Total_Value > 1000) 154 ) 155 156 AND (A.Land_Acres > 0 OR ((A.Num_Bedrooms > 0 OR A.Num_Bathrooms > 0) AND A.Living_Area > 0)) 157 158 ; NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE expression. NOTE: Invalid argument 3 to function SUBSTR. Missing values may be generated. NOTE: Invalid argument 3 to function SUBSTR. Missing values may be generated. NOTE: Invalid argument 3 to function SUBSTR. Missing values may be generated. NOTE: Invalid argument 3 to function SUBSTR. Missing values may be generated. NOTE: Invalid argument 3 to function SUBSTR. Missing values may be generated. NOTE: Invalid argument 3 to function SUBSTR. Missing values may be generated. NOTE: Invalid argument 3 to function SUBSTR. Missing values may be generated. NOTE: Invalid argument 3 to function SUBSTR. Missing values may be generated. ERROR: Insufficient space in file WORK.'SASTMP-000000037'n.UTILITY. ERROR: File WORK.'SASTMP-000000037'n.UTILITY is damaged. I/O processing did not complete. NOTE: Error was encountered during utility-file processing. You may be able to execute the SQL statement successfully if you allocate more space to the WORK library. ERROR: There is not enough WORK disk space to store the results of an internal sorting phase. ERROR: An error has occurred. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 159 QUIT; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 6.84 seconds cpu time 4.77 seconds