Hi   I have run the code and it created new variables for me with no error.  I am using SAS EG 5.1   Run the same code again and I am getting errors now. Not sure which variable is initialised wrong. I highlighted the errors at the bottom of the code, it says it could be some symbol or syntax issue,  this code ran with no errors at the first time and now 2nd time it has this error so I am not sure whats changed and how to pinpoint the error in the particular line,  I am not sure what is the problem I need your help to mitigate this.       GOPTIONS ACCESSIBLE; 15 data tk.SBOS_final; 16 set tk.application_fj; 17 where final_decision_flag not in ('Withdrawn'); 18 19 20 21 format Final_Decision $10. ; 22 Format ast 1.; 23 Format bureau 1.; 24 Format affordability 1.; 25 Format collatoral 1.; 26 Format fraud 1.; 27 Format general 1.; 28 Format internalconduct 1.; 29 Format purpose 1.; 30 Format borrower 1.; 31 Format forcerefer 1.; 32 Format expense 1.; 33 Format collatoral_refer 1.; 34 Format character_refer 1.; 35 Format policy_refer 1.; 36 Format capacity_refer 1.; 37 38 format sdate date9.; 39 Format SI_Final $25.; 40 41 ast = 0; 42 bureau = 0; 43 affordability = 0; 44 collatoral = 0; 45 fraud = 0; 46 general= 0; 47 internalconduct = 0; 48 purpose = 0; 49 borrower =0; 50 forcerefer= 0; 51 expense= 0; 52 collatoral_refer = 0; 53 character_refer = 0; 54 policy_refer = 0; 55 capacity_refer = 0; 2 The SAS System 11:20 Friday, January 26, 2018 56 57 ac11= 0; 58 db21= 0; 59 ra03 = 0; 60 61 av11= 0; 62 db23 = 0; 63 ra01 = 0; 64 rb21 = 0; 65 rb91 = 0; 66 rb95 = 0; 67 rb96 = 0; 68 rc01 = 0; 69 re51 = 0; 70 rf01 = 0; 71 rf90 = 0; 72 rg01 = 0; 73 rg15 = 0; 74 rg16 = 0; 75 rg17 = 0; 76 ri01 = 0; 77 ri02 = 0; 78 ri04 = 0; 79 ri25 = 0; 80 ri31 = 0; 81 ri33 = 0; 82 ri45 = 0; 83 ri46 = 0; 84 rp03 = 0; 85 rp06 = 0; 86 rp07 = 0; 87 rp08 = 0; 88 rp10 = 0; 89 rp12 = 0; 90 avxx = 0; 91 rfxx = 0; 92 rg18 = 0; 93 rg19 = 0; 94 95 F_Medico = 0; 96 Franchiseflag_f = 0; 97 98 99 100 array RB_PostBurReasonCdeTable_[21] RB_PostBurReasonCdeTable_01 - RB_PostBurReasonCdeTable_21; 101 102 /*Application level*/ 103 104 do i = 1 to 21; 105 if RB_PostBurReasonCdeTable_[i] in ('AC11') then ac11 = 1; 106 if RB_PostBurReasonCdeTable_[i] in ('DB21') then db21 = 1; 107 if RB_PostBurReasonCdeTable_[i] in ('RA03','RA93') then ra03 = 1; 108 if RB_PostBurReasonCdeTable_[i] in ('RA84','DB23') then db23 = 1; 109 if RB_PostBurReasonCdeTable_[i] in ('RA51','RA83','RA92','RA93','RR12','RR13','RA03') then ra01 = 1; 110 if RB_PostBurReasonCdeTable_[i] in ('RA97','RK01','RK02','RK03','RK04','RK05','RK06','RK07','RB21') then rb21 = 1; 111 if RB_PostBurReasonCdeTable_[i] in ('RB91') then rb91 = 1; 112 if RB_PostBurReasonCdeTable_[i] in ('RB95') then rb95 = 1; 113 if RB_PostBurReasonCdeTable_[i] in ('RB96') then rb96 = 1; 3 The SAS System 11:20 Friday, January 26, 2018 114 if RB_PostBurReasonCdeTable_[i] in ('RC01') then rc01 = 1; 115 if RB_PostBurReasonCdeTable_[i] in ('RF01', 'RF02', 'RF03','RF90', 'RK10','RK11','RF91', 'RF92', 'RF95') then rfxx = 1; 116 117 if RB_PostBurReasonCdeTable_[i] in ('RA96','RE51') then re51 = 1; 118 119 if RB_PostBurReasonCdeTable_[i] in ('RA01') then rg01 = 1; 120 if RB_PostBurReasonCdeTable_[i] in ('RG18') then rg18 = 1; 121 if RB_PostBurReasonCdeTable_[i] in ('RG19') then rg19 = 1; 122 123 if RB_PostBurReasonCdeTable_[i] in ('RA29','RG15') then rg15 = 1; 124 if RB_PostBurReasonCdeTable_[i] in ('RA21','RR11','RG16') then rg16 = 1; 125 if RB_PostBurReasonCdeTable_[i] in ('RA48','RA49','RA52','RG17') then rg17 = 1; 126 if RB_PostBurReasonCdeTable_[i] in ('RA50','RR16') then ri01 = 1; 127 if RB_PostBurReasonCdeTable_[i] in ('RI02','RR16') then ri02 = 1; 128 if RB_PostBurReasonCdeTable_[i] in ('RI03') then ri03 = 1; 129 if RB_PostBurReasonCdeTable_[i] in ('RI04','RA54') then ri04 = 1; 130 if RB_PostBurReasonCdeTable_[i] in ('RI25') then ri25 = 1; 131 if RB_PostBurReasonCdeTable_[i] in ('RI31') then ri31 = 1; 132 if RB_PostBurReasonCdeTable_[i] in ('RA89','RI33') then ri33 = 1; 133 if RB_PostBurReasonCdeTable_[i] in ('RI45') then ri45 = 1; 134 if RB_PostBurReasonCdeTable_[i] in ('RI46') then ri46 = 1; 135 136 if RB_PostBurReasonCdeTable_[i] in ('RP01','RP02','RP04','RP05','RP06','RP07','RP08','RP03','RA99') then rp03 = 1; 137 if RB_PostBurReasonCdeTable_[i] in ('RA54','RA55','RA56','RP06') then rp06 = 1; 138 if RB_PostBurReasonCdeTable_[i] in ('RA57','RP07') then rp07 = 1; 139 if RB_PostBurReasonCdeTable_[i] in ('RP08') then rp08 = 1; 140 if proposedTBL > 1000000 then rp08 = 1; 141 if RB_PostBurReasonCdeTable_[i] in ('RA85','RP10') then rp10 = 1; 142 if RB_PostBurReasonCdeTable_[i] in ('RA14','RP12') then rp12 = 1; 143 if RB_PostBurReasonCdeTable_[i] in ('RA94','RA90','RA88','RA95','AV16','AV15','AV11','AV12','AV13','AV14','AV17') then 143 ! avxx = 1; 144 145 146 /*category level*/ 147 148 149 150 if RB_PostBurReasonCdeTable_[i] in 150 ! ('AC11','AV11','AV12','AV13','AV15','AV16','AV17','RC01','RE51','RG16','RA90','RA88','RR11','RA21','RA94','RA95') then 150 ! collatoral_refer = 1; 151 if RB_PostBurReasonCdeTable_[i] in 151 ! ('DB01','DB23','RB21','RB91','RB95','RB96','RF01','RF02','RB03','RF90','RF91','RF92','RF95','RG19','RI01','RI02','RI04',' 151 ! RI25','RI31','RI33','RI45','RI46','DA01','RA50','RA84','RA97','RK01','RK02','RK03','RK04','RK05','RK06','RK07','RK10','RK 151 ! 11')then Character_refer = 1; 152 if RB_PostBurReasonCdeTable_[i] in 152 ! ('SB05','AV14','MG04','RG15','RG18','RP03','RP04','RP06','RP07','RP08','RP09','RP10','RP11','RP12','RW21','RP05','RA98',' 152 ! RA14','RA29','RA54','RA55','RA56','RA57','RA99','RP01','RP02')then Policy_refer = 1; 153 if RB_PostBurReasonCdeTable_[i] in ('RA01','RA03','RA51','RA83','RA92','RA93','RR12','RR13') then Capacity_refer = 1; 154 155 /*Category 2 Level */ 156 157 if RB_PostBurReasonCdeTable_[i] in ('AC11','AV11','AV12','AV13','AV14','AV15','AV16','AV17') then ast = 1; 158 if RB_PostBurReasonCdeTable_[i] in ('DB21','DB23','RB21','RB91','RB95','RB96') then bureau = 1; 159 if RB_PostBurReasonCdeTable_[i] in ('RA01','RA03') then affordability = 1; 160 if RB_PostBurReasonCdeTable_[i] in ('RC01') then collatoral = 1; 161 if RB_PostBurReasonCdeTable_[i] in ('RF01','RF02','RF03','RF90','RF91','RF92','RF93') then fraud = 1; 162 if RB_PostBurReasonCdeTable_[i] in ('RG15','RG16','RG17','RG18','RG19') then general = 1; 163 if RB_PostBurReasonCdeTable_[i] in ('RI01','RI02','RI04','RI25','RI31','RI33','RI45','RI46') then Internalconduct = 1; 4 The SAS System 11:20 Friday, January 26, 2018 164 if RB_PostBurReasonCdeTable_[i] in ('RP03','RP04','RP05','RP06','RP07','RP08','RP09','RP10','RP11','RP12') then purpose = 164 ! 1; 165 if RB_PostBurReasonCdeTable_[i] in ('RW21') then borrower = 1; 166 if RB_PostBurReasonCdeTable_[i] in ('MG03','MG04','RG01') then forcerefer = 1; 167 if RB_PostBurReasonCdeTable_[i] in ('RE51') then expense = 1; 168 169 sdate = datepart(submitted_date); 170 month = month(sdate); 171 year = year(sdate); 172 datestamp = year*100+month; 173 174 if AssessmentDecision in ( 'Approved') then Final_Decision = 'Approved'; 175 else Final_Decision = final_decision_flag; 176 177 178 end; 179 180 If CH_ANZSIC in ( 8621, 8622, 8623 , 8640, 8635) then F_Medico = 1 ; 181 182 if proposed_si in ('A','B','C') then SI_final = 'Fully Secured'; 183 184 else if proposed_si in ('D','E','F') then SI_final = 'Partially Secured'; 185 186 else SI_final = 'Unsecured'; 187 188 if AppSystem in ('SBLoans', ‘SBOS’ ) then do; _ 22 76 ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, a missing value, iterator, (. ERROR 76-322: Syntax error, statement will be ignored. 189 190 if ApplicationNature = 'ANZ Preferred Franchising Loan' or AC_OPERATEASAFRANCHISEFLAG = "Y" then FranchiseFlag_f = 1 _ 390 200 76 ERROR 390-185: Expecting an relational or arithmetic operator. ERROR 200-322: The symbol is not recognized and will be ignored. ERROR 76-322: Syntax error, statement will be ignored. 191 192 end; 193 194 195 RUN; NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 180:4 NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set TK.SBOS_FINAL may be incomplete. When this step was stopped there were 0 observations and 189 variables. WARNING: Data set TK.SBOS_FINAL was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): 5 The SAS System 11:20 Friday, January 26, 2018 real time 4.58 seconds cpu time 0.01 seconds ... View more

Hi   I have written this code to create a summary with number of apps approved in each category and total number of apps in the respective category so that I can run pivot table to create %age of each type of app,  no of apps approved by each type/ total no of apps for each type * 100   my question is , is there any better way of writing this proc sql code:  please advise.   proc sql; create table tk.summary_category2_level as select   app_type_flag , ast, bureau , collatoral, affordability, fraud , general , internalconduct , purpose , borrower, forcerefer, expense, count(*) as num_apps ,sum(case when final_decision = 'Approved' and ast = 1 then 1 else 0 end) as AST_approved ,sum(case when final_decision = 'Approved' and bureau =1 then 1 else 0 end) as BUREAU_approved ,sum(case when final_decision = 'Approved' and collatoral =1 then 1 else 0 end) as COLLATORAL_approved ,sum(case when final_decision = 'Approved' and affordability =1 then 1 else 0 end) as AFFORDABILITY_approved ,sum(case when final_decision = 'Approved' and fraud =1 then 1 else 0 end) as FRAUD_approved ,sum(case when final_decision = 'Approved' and general =1 then 1 else 0 end) as GENERAL_approved ,sum(case when final_decision = 'Approved' and internalconduct =1 then 1 else 0 end) as INTERNALCONDUCT_approved ,sum(case when final_decision = 'Approved' and purpose =1 then 1 else 0 end) as PURPOSE_approved ,sum(case when final_decision = 'Approved' and borrower =1 then 1 else 0 end) as BORROWER_approved ,sum(case when final_decision = 'Approved' and forcerefer =1 then 1 else 0 end) as FORCEREFER_approved ,sum(case when final_decision = 'Approved' and expense =1 then 1 else 0 end) as EXPENSE_approved   ,sum(ast) as Total_AST ,sum(bureau) as Total_BUREAU , sum(collatoral) as Total_COLLATORAL , sum(affordability) as Total_AFFORDABILITY , sum(fraud) as Total_FRAUD , sum(general) as Total_GENERAL , sum(internalconduct) as Total_INTERNALCONDUCT , sum(purpose) as Total_PURPOSE , sum(borrower) as Total_BORROWER , sum(forcerefer) as Total_FORCEREFER , sum(expense) as Total_EXPENSE     from   tk.SBOS_final   group by   app_type_flag , ast, bureau , collatoral, affordability, fraud, general, internalconduct, purpose, borrower, forcerefer, expense ; quit;   ... View more

Hi   I am writing this code and it comes up with this error. using where statement with proc sql...where statement is creating error.   Please advise,   proc sql; 16 create table summary5 as 17 select 18 state , capacity_refer 19 ,count(*) as num_apps 20 21 where capacity_refer = '1' from sbos_sbl_category group by   state , capacity_refer ; quit;   _____ 22 76 ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN, LENGTH, TRANSCODE. ERROR 76-322: Syntax error, statement will be ignored. ... View more

Hi   I am trying to run these do loops on this data , there are 21 variables and under each variable some credit codes apply,  my query is find the volume of these rules on these variables.   Three rules are : Affordability,  Character and Policy. How many application have affordability or character or policy hits.   Under each rule there are certain codes which determine that these rules are applied on the application.   I have written this code for each rule and then run proc freq to get the volumes.   Question 1:  Is there a better way to write do loops for these rules as I might have to add more rules later on. As there are 21 variables to test these rules , is there any other way to combine them or not.   Question 2:  if I have to exclude those applications which are withdrawn, I put where statement in the end, but when I tried to combine 02 statements then it gives me error: like if I say where assessordecision <> "withdrawn"  and <> ' ' ;  it works if I exclude the missing value and keep it to withdrawn only, how do I combine the statements with where.   code is attached as an attachment its a long one.     ... View more

  Hi   I have a question :   I have got this data which I am using as text file input:   data ORION.LOCATIONRAWFILE;   infile datalines dsd truncover;   input price:$15.  Address:$20. Style:$8. Zip:32. Bedroom:32. Baths:32.; datalines4; "64,000",sheppard Avenue,Ranch,1250,2,1 "65,850",Rand Street,Split,1190,1,1 "80,050",Market Street,Condo,1400,2,1.5 "107,250",Garris Street,TwoStory,1810,4,3 "86,650",Kemble Avenue,Ranch,1500,3,3 "94,450",West Drive,Split,1615,4,3 "73,650",Graham Avenue,Split,1305,3,1.5 ;;;;   I am reading this txt file and Ii gives me the desired result that I want.   data orion.locationrawfile; infile "/folders/myfolders/pg2/location.txt"  dlm=',' dsd ; length Address $20; input Style $  Zip Bedroom   Baths  Address  $  Price : dollar8. ;   proc print data = orion.locationrawfile noobs; var Style Zip Bedroom Baths Address Price; format price dollar8.; format baths best.; run;   Question:  I am using the Colon input with Price ,  I am using the length statement for address:   If I remove length statement for address and try to use & sign with the character length $20, it gives as error, Is that possible to remove the length statement and use code as below:   data orion.locationrawfile; infile "/folders/myfolders/pg2/location.txt"  dlm=',' dsd ; input Style $  Zip Bedroom   Baths  Address & $20.  Price : dollar8. ;   I got an error in the output.   Please advise if & works like this or some other modification is required without using the length statement.   thanks TK   ... View more