JovanaUniCredit Tracker
https://communities.sas.com/kntur85557/tracker
JovanaUniCredit TrackerTue, 23 Jul 2024 16:47:20 GMT2024-07-23T16:47:20ZCategorization of continuous variables
https://communities.sas.com/t5/Statistical-Procedures/Categorization-of-continuous-variables/m-p/745410#M36312
<P>Hi community!</P><P>I have to do a <U><EM>categorization for continuous factors</EM></U> (e.g. using deciles, or different intervals consistent with the sample size) in order to provide more stability to the analysis of their relation with the target variable (which is not binary, but interval one).</P><P>If I decide to use deciles, each bucket should be composed by at least 30 observation in order to perform following discriminatory power test.</P><P> </P><P>Is it possible to be done in Enterprise Miner?</P><P> </P><P>I saw that there is Interactive Binning node, where interval target variable is converted to binary one. Is it correct to use this node, or there is other node more appropriate for this task?</P><P> </P><P>In addition to this, if it is not possible to be done in Enterprise Miner, how it can be done in SAS EG?</P><P> </P><P>P.S. If this is not correct forum to ask this question, please tell me which one is in order to get answer.</P><P> </P><P>Thank you, Jovana</P>Thu, 03 Jun 2021 08:25:36 GMThttps://communities.sas.com/t5/Statistical-Procedures/Categorization-of-continuous-variables/m-p/745410#M36312JovanaUniCredit2021-06-03T08:25:36ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/644167#M192369
Never mind. I will think something up. I think that you don't understand what is my problem that I have to code it.<BR /><BR />Anyway, thank you for help for previous problem.Thu, 30 Apr 2020 08:36:59 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/644167#M192369JovanaUniCredit2020-04-30T08:36:59ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/644163#M192365
<P> But that is not correct result!</P><P> </P><P>My result should be the result in which is all three conditions fulfilled:</P><P>ee < 0.01</P><P>err_stage2 is minimal</P><P>dist_prov is minimal</P><P> </P><P>Therefore I need to put all three conditions.</P><P> </P><P>In the first step:</P><P>distance and err_prev should be set to 1E6;</P><P>in next step if condition ee < 0.01 and err_stage2<err_prev, then err_stage should be set to te err_stage2 from the first step. In case err_stage2 = err_prev and dist_prov < distance, then err_stage should be set to te err_stage2 from the fist step and distance should be set to dist_prov from the first step.</P><P> </P><P>This shoul be running until non of conditions are met, and the code should return previous observation.</P>Thu, 30 Apr 2020 07:12:49 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/644163#M192365JovanaUniCredit2020-04-30T07:12:49ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/644000#M192291
<PRE>data STEP;
infile datalines delimiter=',';
input step $ dist_prov $ err_stage2 $ ee;
datalines;
0.0018,0.713688038,-21,0.0008025069
0.0108,0.5895856469,-97,0.0037068175
0.0109,0.589033195,-97,0.0037068175
0.011,0.588498118,-97,0.0037068175
0.0111,0.5879803893,-100,0.0038214613
0.0112,0.5874800437,-101,0.0038596759
0.0113,0.5869970738,-101,0.0038596759
0.0114,0.5865313784,-102,0.0038978906
0.0115,0.5860828259,-102,0.0038978906
0.0116,0.5856514384,-103,0.0039361052
0.0117,0.5852372379,-103,0.0039361052
0.0019,0.7114179585,-22,0.0008407215
0.0118,0.5848402043,-104,0.0039743198
0.0119,0.5844602398,-104,0.0039743198
0.012,0.5840973151,-104,0.0039743198
0.0121,0.5837514453,-104,0.0039743198
0.0122,0.5834226505,-104,0.0039743198
0.0123,0.5831109592,-104,0.0039743198
0.0124,0.5828164182,-104,0.0039743198
0.0125,0.5825389541,-104,0.0039743198
0.0126,0.5822784975,-105,0.0040125344
0.0127,0.5820350474,-105,0.0040125344
0.002,0.7091705406,-22,0.0008407215
0.0128,0.5818086221,-105,0.0040125344
0.0129,0.5815993391,-106,0.004050749
0.013,0.5814072808,-106,0.004050749
0.0131,0.5812324477,-109,0.0041653928
0.0132,0.5810747073,-111,0.0042418221
0.0133,0.5809340598,-111,0.0042418221
0.0134,0.5808108228,-111,0.0042418221
0.0135,0.5807051824,-111,0.0042418221
0.0136,0.580617169,-112,0.0042800367
0.0137,0.5805464088,-112,0.0042800367
0.0021,0.7069457815,-23,0.0008789361
0.0138,0.580492745,-112,0.0042800367
0.0139,0.5804560056,-112,0.0042800367
0.014,0.5804362434,-112,0.0042800367
0.0141,0.5804334986,-113,0.0043182513
0.0142,0.5804477747,-113,0.0043182513
0.0143,0.580479013,-113,0.0043182513
0.0144,0.5805272225,-113,0.0043182513
0.0145,0.5805924352,-113,0.0043182513
0.0146,0.580674683,-116,0.0044328951
0.0147,0.5807740923,-116,0.0044328951
0.0022,0.7047432879,-25,0.0009553653
0.0148,0.5808907457,-116,0.0044328951
0.0149,0.5810246481,-116,0.0044328951
0.015,0.5811757607,-116,0.0044328951
0.0151,0.5813440406,-116,0.0044328951
0.0152,0.5815294066,-116,0.0044328951
0.0153,0.5817318128,-116,0.0044328951
0.0154,0.5819512482,-116,0.0044328951
0.0155,0.5821876916,-116,0.0044328951
0.0156,0.5824410173,-116,0.0044328951
0.0157,0.5827112049,-116,0.0044328951
0.0023,0.7025629119,-26,0.0009935799
0.0158,0.5829982404,-116,0.0044328951
0.0159,0.5833022193,-116,0.0044328951
0.016,0.583623084,-116,0.0044328951
0.0161,0.5839604872,-116,0.0044328951
0.0162,0.5843140187,-116,0.0044328951
0.0163,0.5846835963,-116,0.0044328951
0.0164,0.5850691749,-116,0.0044328951
0.0165,0.5854707041,-117,0.0044711098
0.0166,0.5858877426,-118,0.0045093244
0.0167,0.5863201851,-118,0.0045093244
0.0024,0.7004047561,-31,0.001184653
0.0168,0.5867679921,-118,0.0045093244
0.0169,0.587231135,-118,0.0045093244
0.017,0.5877096111,-118,0.0045093244
0.0171,0.5882034291,-118,0.0045093244
0.0172,0.5887125685,-118,0.0045093244
0.0173,0.589236972,-118,0.0045093244
0.0174,0.5897765904,-118,0.0045093244
0.0174,0.5897765904,-118,0.0045093244
0.0175,0.5903313671,-118,0.0045093244
0.0176,0.5909011571,-120,0.0045857536
0.0025,0.6982688974,-31,0.001184653
0.0177,0.5914858743,-120,0.0045857536
0.0178,0.5920855059,-120,0.0045857536
0.0179,0.5927000213,-120,0.0045857536
0.018,0.5933293857,-120,0.0045857536
0.0181,0.59397357,-120,0.0045857536
0.0182,0.5946325383,-120,0.0045857536
0.0183,0.5953062513,-121,0.0046239682
0.0184,0.59599464,-121,0.0046239682
0.0185,0.5966976499,-121,0.0046239682
0.0186,0.5974153152,-121,0.0046239682
0.0026,0.6961553577,-33,0.0012610822
0.0187,0.5981474667,-121,0.0046239682
0.0188,0.5988937947,-121,0.0046239682
0.0189,0.5996542733,-121,0.0046239682
0.019,0.6004289303,-122,0.0046621828
0.0191,0.6012179197,-122,0.0046621828
0.0192,0.602021214,-122,0.0046621828
0.0193,0.602838726,-122,0.0046621828
0.0194,0.6036703648,-122,0.0046621828
0.0195,0.6045161413,-122,0.0046621828
0.0196,0.6053762111,-122,0.0046621828
0.0027,0.6940639582,-36,0.0013757261
0.0197,0.6062504979,-122,0.0046621828
0.0198,0.6071389425,-124,0.004738612
0.0199,0.6080414891,-124,0.004738612
0.02,0.6089580546,-124,0.004738612
0.0201,0.6098885958,-124,0.004738612
0.0202,0.6108330459,-124,0.004738612
0.0203,0.6117913342,-124,0.004738612
0.0204,0.6127636336,-124,0.004738612
0.0205,0.6137501761,-124,0.004738612
0.0206,0.6147505274,-124,0.004738612
0.001,0.7326723142,-12,0.0004585754
0.0028,0.6919946245,-36,0.0013757261
0.0207,0.6157645942,-124,0.004738612
0.0208,0.6167922683,-127,0.0048532559
0.0209,0.6178335216,-127,0.0048532559
0.021,0.6188883503,-127,0.0048532559
0.0211,0.6199567182,-127,0.0048532559
0.0212,0.6210385425,-127,0.0048532559
0.0213,0.6221337216,-127,0.0048532559
0.0214,0.6232421898,-127,0.0048532559
0.0215,0.6243638879,-127,0.0048532559
0.0216,0.6254986921,-127,0.0048532559
0.0029,0.6899472953,-37,0.0014139407
0.0217,0.6266465883,-127,0.0048532559
0.0218,0.6278075507,-127,0.0048532559
0.0219,0.6289814866,-127,0.0048532559
0.022,0.6301683119,-127,0.0048532559
0.0221,0.6313678824,-127,0.0048532559
0.0222,0.6325798847,-127,0.0048532559
0.0223,0.633804373,-127,0.0048532559
0.0224,0.6350417467,-127,0.0048532559
0.0225,0.6362919826,-128,0.0048914705
0.0226,0.6375550963,-128,0.0048914705
0.003,0.6879216749,-37,0.0014139407
0.0227,0.6388310724,-128,0.0048914705
0.0228,0.6401197903,-128,0.0048914705
0.0229,0.6414211309,-128,0.0048914705
0.023,0.6427350077,-129,0.0049296851
0.0231,0.6440613258,-133,0.0050825436
0.0232,0.6454000338,-139,0.0053118312
0.0233,0.6467511525,-139,0.0053118312
0.0234,0.648114648,-139,0.0053118312
0.0235,0.6494904548,-139,0.0053118312
0.0236,0.6508783126,-139,0.0053118312
0.0031,0.6859176687,-41,0.0015667991
0.0237,0.6522780045,-139,0.0053118312
0.0238,0.6536894544,-139,0.0053118312
0.0239,0.655112577,-139,0.0053118312
0.024,0.6565472328,-139,0.0053118312
0.0241,0.6579933356,-139,0.0053118312
0.0242,0.6594507924,-139,0.0053118312
0.0243,0.660919541,-139,0.0053118312
0.0244,0.6623994857,-139,0.0053118312
0.0245,0.663890631,-139,0.0053118312
0.0246,0.6653929524,-140,0.0053500459
0.0032,0.6839353239,-42,0.0016050138
0.0247,0.6669063126,-140,0.0053500459
0.0248,0.6684306613,-140,0.0053500459
0.0249,0.6699659195,-140,0.0053500459
0.0033,0.6819747122,-43,0.0016432284
0.0034,0.6800358955,-44,0.001681443
0.0035,0.678118918,-45,0.0017196576
0.0036,0.676223708,-45,0.0017196576
0.0037,0.6743500794,-45,0.0017196576
0.0011,0.7302183254,-13,0.00049679
0.0038,0.6724978203,-46,0.0017578722
0.0039,0.6706668561,-52,0.0019871599
0.004,0.6688570361,-54,0.0020635891
0.0041,0.6670675998,-54,0.0020635891
0.0042,0.6652980377,-54,0.0020635891
0.0043,0.6635481238,-54,0.0020635891
0.0044,0.6618178068,-54,0.0020635891
0.0045,0.6601071536,-56,0.0021400183
0.0046,0.6584158953,-57,0.002178233
0.0047,0.6567436522,-58,0.0022164476
0.0012,0.7277881194,-14,0.0005350046
0.0048,0.6550905318,-58,0.0022164476
0.0049,0.6534566204,-58,0.0022164476
0.005,0.6518420547,-59,0.0022546622
0.0051,0.6502468857,-59,0.0022546622
0.0052,0.6486711395,-60,0.0022928768
0.0053,0.6471148905,-61,0.0023310914
0.0054,0.6455781515,-61,0.0023310914
0.0055,0.6440607061,-61,0.0023310914
0.0056,0.6425625301,-62,0.002369306
0.0057,0.6410836022,-62,0.002369306
0.0013,0.7253810198,-15,0.0005732192
0.0058,0.6396240639,-62,0.002369306
0.0059,0.6381839194,-65,0.0024839499
0.006,0.6367627444,-66,0.0025221645
0.0061,0.6353604102,-67,0.0025603791
0.0062,0.6339770941,-76,0.0029043106
0.0063,0.6326126929,-82,0.0031335983
0.0064,0.6312667955,-82,0.0031335983
0.0065,0.6299390589,-82,0.0031335983
0.0066,0.6286292918,-82,0.0031335983
0.0067,0.6273375439,-83,0.0031718129
0.0014,0.722996671,-16,0.0006114338
0.0068,0.6260637108,-83,0.0031718129
0.0069,0.6248075526,-83,0.0031718129
0.007,0.6235690708,-83,0.0031718129
0.0071,0.622348405,-83,0.0031718129
0.0072,0.6211456171,-83,0.0031718129
0.0073,0.6199607504,-86,0.0032864567
0.0074,0.6187935948,-86,0.0032864567
0.0075,0.6176438147,-86,0.0032864567
0.0076,0.6165113016,-87,0.0033246714
0.0077,0.6153958048,-87,0.0033246714
0.0015,0.7206351271,-16,0.0006114338
0.0078,0.6142973488,-87,0.0033246714
0.0079,0.6132161784,-87,0.0033246714
0.008,0.6121525688,-87,0.0033246714
0.0081,0.6111068155,-87,0.0033246714
0.0082,0.6100790422,-87,0.0033246714
0.0083,0.6090693428,-87,0.0033246714
0.0084,0.6080776786,-89,0.0034011006
0.0085,0.6071040243,-89,0.0034011006
0.0086,0.6061483192,-90,0.0034393152
0.0087,0.6052104875,-90,0.0034393152
0.0016,0.7182964731,-21,0.0008025069
0.0088,0.6042903653,-90,0.0034393152
0.0089,0.6033880189,-92,0.0035157444
0.009,0.6025034625,-95,0.0036303883
0.0091,0.6016366857,-96,0.0036686029
0.0092,0.6007876556,-96,0.0036686029
0.0093,0.5999562669,-96,0.0036686029
0.0094,0.5991425134,-96,0.0036686029
0.0095,0.5983464205,-96,0.0036686029
0.0096,0.5975678942,-97,0.0037068175
0.0097,0.5968068381,-97,0.0037068175
0.0017,0.7159807906,-21,0.0008025069
0.0098,0.5960632359,-97,0.0037068175
0.0099,0.5953371315,-97,0.0037068175
0.01,0.594628569,-97,0.0037068175
0.0101,0.593937541,-97,0.0037068175
0.0102,0.5932638825,-97,0.0037068175
0.0103,0.5926075631,-97,0.0037068175
0.0104,0.5919685772,-97,0.0037068175
0.0105,0.591346914,-97,0.0037068175
0.0106,0.5907425438,-97,0.0037068175
0.0107,0.5901554495,-97,0.0037068175
;
RUN;</PRE><P>But, what if I have this data. It won't work.</P><P> </P><P>Therefore, I think I have to add this kind of code/condition:</P><PRE><CODE class=" language-sas">if ee < 0.01 then do;
IF err_stage2 < err_prev then do;
beta = step;
err_prev = err_stage2;
distance = dist_prov;
new_quantile = 1-(SUM_StgChange_CT)/26168;
change_quant = (SUM_StgChange_CT-SUM_StgChange)/26168;
end;
else if (err_stage2=err_prev) & (dist_prov<distance) then do;
beta = step;
distance = dist_prov;
new_quantile = 1-(SUM_StgChange_CT)/26168;
change_quant = (SUM_StgChange_CT-SUM_StgChange)/26168;
end;</CODE></PRE><P> </P><P>However, sill don't know how to create code that result in one observation.</P><P> </P>Wed, 29 Apr 2020 15:36:14 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/644000#M192291JovanaUniCredit2020-04-29T15:36:14ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643974#M192273
<PRE>data step;
infile datalines delimiter=',';
input step $ dist_prov $ err_stage2 $ ee;
datalines;
0,0.758514812,10,0.000382146
0.0018,0.713688038,-21,0.0008025069
0.0108,0.5895856469,-97,0.0037068175
0.0109,0.589033195,-97,0.0037068175
0.011,0.588498118,-97,0.0037068175
0.0111,0.5879803893,-100,0.0038214613
0.0112,0.5874800437,-101,0.0038596759
0.0113,0.5869970738,-101,0.0038596759
0.0114,0.5865313784,-102,0.0038978906
0.0115,0.5860828259,-102,0.0038978906
0.0116,0.5856514384,-103,0.0039361052
0.0117,0.5852372379,-103,0.0039361052
0.0019,0.7114179585,-22,0.0008407215
;
run;</PRE><P>This is my data step.</P><P> </P><P>Expected result: step = 0.0117</P><P> </P><P>Is it ok like this?</P><P> </P><P>I am filing like learning it.</P>Wed, 29 Apr 2020 14:39:39 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643974#M192273JovanaUniCredit2020-04-29T14:39:39ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643826#M192192
Well, my final step that I will choose for solution of my problem is the step which meet next conditions:<BR />ee < 0.01;<BR />err_stage2 is minimal for that step;<BR />prov _prov is minimal for that step;<BR /><BR />In the first line, I will set err_prev and distance to some big number, and in every next line I will set new values for err_prev and distance which are equal to err_stage2 and dist_prov of the previous step/row.<BR /><BR />That was my idea.Wed, 29 Apr 2020 08:34:45 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643826#M192192JovanaUniCredit2020-04-29T08:34:45ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643786#M192185
Thank You! This means a lot to me since I still don't have so much experience and knowledge with macros in SAS and programing in SAS in general!<BR /><BR />It works now!<BR /><BR />Finally, I can see the table like this:<BR />step sum_StgChange SUM_StgChange_CT SSQ2 dist_prov err_stage2 ee<BR />0 2608 2618 0.57534472 0.758514812 10 0.000382146<BR />0.0018 2608 2587 0.509350616 0.713688038 -21 0.000802507<BR />0.0108 2608 2511 0.347611235 0.589585647 -97 0.003706818<BR />0.0109 2608 2511 0.346960105 0.589033195 -97 0.003706818<BR /><BR />And now I want some optimization code with some conditions to be fulfilled and as result of the code I just want to see one line with most optimal step.<BR /><BR />I tried to do it with this code:<BR /><BR />%LET distance = 1E6;<BR />%LET err_prev = 1E6;<BR />%LET new_quantile = 0;<BR />%LET change_quant = 0;<BR />%LET beta = 0;<BR /><BR />DATA BETA_FINAL;<BR />SET bete;<BR /><BR />err_prev = 1E6;<BR />IF ee < 0.01 THEN DO;<BR />IF(err_stage2 < err_prev) THEN DO;<BR />beta = step;<BR />err_prev = err_stage2;<BR />distance = dist_prov;<BR />new_quantile = 1-(SUM_StgChange_CT)/26168;<BR />change_quant = (SUM_StgChange_CT-SUM_StgChange)/26168;<BR />end;<BR />else if (err_stage2=err_prev) & (dist_prov<distance) then do;<BR />beta = step;<BR />distance = dist_prov;<BR />new_quantile = 1-(SUM_StgChange_CT)/26168;<BR />change_quant = (SUM_StgChange_CT-SUM_StgChange)/26168;<BR />end;<BR />END;<BR />RUN;<BR /><BR />but it won't work. It will calculate all from the code but for every step, for every row in the table. And I don't want that, I want just one line/row where all conditions are fulfilled.<BR /><BR />Do you have any suggestions how to do this?<BR /><BR />Thank you!<BR /><BR />Wed, 29 Apr 2020 07:14:49 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643786#M192185JovanaUniCredit2020-04-29T07:14:49ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643622#M192123
<P>Great!</P><P> </P><P>Last but not the least, when this is finished I don't have any output.</P><P> </P><P>Particularly, I am creating tables in makro- 242 tables, that finally, I want to join, and I would do like you already proposed by running:</P><P> </P><P>data bete;<BR />set ctage_obs:;<BR />run;</P><P> </P><P>But it won't work, unfortinately.</P><P> </P><P>What is the problem?</P><P> </P><P>This is the code I run:</P><P> </P><P>%macro makro(korak,broj);<BR />DATA CTAge;<BR />SET obs_stg_change;<BR />step=((1-alpha)/log(50))/(50-1);<BR />y= alpha+ &korak*log(Age);<BR />IF(I1=1) THEN min_Threshold = min(y,PDThreshold);<BR />IF(I2=1) THEN max_Threshold = max(y,PDThreshold);<BR />min_max_Threshold=COALESCE(min_Threshold, max_Threshold);<BR />IF PDPuncCurr > min_max_Threshold THEN StgChange_CT = 1; ELSE StgChange_CT = 0;<BR />RUN;</P><P>PROC SQL;<BR />CREATE TABLE CTAge_obs&broj AS<BR />SELECT &korak AS step, min(t1.sum_StgChange) as sum_StgChange,<BR />(SUM(t1.StgChange_CT)) AS SUM_StgChange_CT, /*a1*//* count # of obs which change stage */<BR />/* (SUM(t1.StgChange_CT) - t1.sum_StgChange) AS PROVERA,*/<BR />/* (SUM(abs(t1.StgChange_CT-t1.StgChange))) AS SUM_StgChange_DIFF,*/ /*Calculate distance between old CT and new one */<BR />(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS SSQ2, /*sum of square*/<BR />SQRT(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS dist_prov, /* square root of the distance */<BR />ABS(SUM(t1.StgChange_CT) - min(t1.sum_StgChange)) AS err_stage2, /* Sum of obs which change stage */<BR />ABS(SUM(t1.StgChange_CT)-sum(t1.StgChange))/MIN(t1.no_obs) AS ee<BR />FROM WORK.CTAge t1;<BR />QUIT;<BR />%mend;</P><P> </P><P>%macro makro(i,j);<BR />%put &=i,&=j;<BR />%mend;</P><P>data _null_;<BR />call execute('%nrstr(%makro(0,1))');<BR />i = .001;<BR />do j = 2 to 242;<BR />call execute(cats('%nrstr(%makro(',i,',',j,'))'));<BR />i = i + .0001;<BR />end;<BR />run;</P><P>data bete;<BR />set ctage_obs:;<BR />run;</P><P> </P><P>And I can see error in my log:</P><P> </P><P>ERROR: The data set list (WORK.ctage_obs:) does not contain any members.</P>Tue, 28 Apr 2020 14:42:04 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643622#M192123JovanaUniCredit2020-04-28T14:42:04ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643610#M192118
<P>Again, this is not what I wanted, because when I run it I can see that for one I value there is j different tables and I don't want that.</P><P> </P><P>I just want that every I value has it's index. I have 242 different I values so I need 242 different coefficients for created tables, for every I. Whenever I value is changes, also j values has to be changed to next integer. So:</P><P> </P><P>i j</P><P>0 1</P><P>0.001 2</P><P>0.0011 3</P><P>0.0012 4</P><P> </P><P>and so on.</P><P> </P><P>Any suggestion?</P>Tue, 28 Apr 2020 13:59:52 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643610#M192118JovanaUniCredit2020-04-28T13:59:52ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643599#M192115
<P>Actually, the idea behind this was to add j in order to have better naming for every table, so as to know which table is for which step and how to join them. </P><P> </P><P>Do You have any suggestion how to change it?</P><P> </P><P>The code that I wrote is:</P><P> </P><P>%macro makro(korak,broj);<BR />DATA CTAge;<BR />SET obs_stg_change;<BR />step=((1-alpha)/log(50))/(50-1);<BR />y= alpha+ &korak*log(Age);<BR />IF(I1=1) THEN min_Threshold = min(y,PDThreshold);<BR />IF(I2=1) THEN max_Threshold = max(y,PDThreshold);<BR />min_max_Threshold=COALESCE(min_Threshold, max_Threshold);<BR />IF PDPuncCurr > min_max_Threshold THEN StgChange_CT = 1; ELSE StgChange_CT = 0;<BR />RUN;</P><P>/*0.5757232999 SUM SQUARE*/</P><P>PROC SQL;<BR />CREATE TABLE CTAge_obs&broj AS<BR />SELECT &korak AS step, min(t1.sum_StgChange) as sum_StgChange,<BR />(SUM(t1.StgChange_CT)) AS SUM_StgChange_CT, /*a1*//* count # of obs which change stage */<BR />/* (SUM(t1.StgChange_CT) - t1.sum_StgChange) AS PROVERA,*/<BR />/* (SUM(abs(t1.StgChange_CT-t1.StgChange))) AS SUM_StgChange_DIFF,*/ /*Calculate distance between old CT and new one */<BR />(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS SSQ2, /*sum of square*/<BR />SQRT(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS dist_prov, /* square root of the distance */<BR />ABS(SUM(t1.StgChange_CT) - min(t1.sum_StgChange)) AS err_stage2, /* Sum of obs which change stage */<BR />ABS(SUM(t1.StgChange_CT)-sum(t1.StgChange))/MIN(t1.no_obs) AS ee<BR />FROM WORK.CTAge t1;<BR />QUIT;<BR />%mend;</P><P> </P><P>%makro(0,1);<BR />%makro(0.001,2);<BR />%makro(0.0011,3);<BR />%makro(0.0012,4);<BR />%makro(0.0013,5);<BR />%makro(0.0014,6);<BR />%makro(0.0015,7);<BR />%makro(0.0016,8);<BR />%makro(0.0017,9);<BR />%makro(0.0018,10);<BR />%makro(0.0019,11);<BR />%makro(0.002,12);<BR />%makro(0.0021,13);<BR />%makro(0.0022,14);<BR />%makro(0.0023,15);<BR />%makro(0.0024,16);<BR />%makro(0.0025,17);<BR />%makro(0.0026,18);<BR />%makro(0.0027,19);</P><P>...</P><P> </P><P>and so on until the 242. table and:</P><P> </P><P>data bete;<BR />set ctage_obs:;<BR />run;</P><P> </P><P>Do You have any suggestion what would be the best solution instead of writting %makro(x,y); for 242 times?</P><P> </P><P>Thanks a lot!</P>Tue, 28 Apr 2020 13:22:57 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643599#M192115JovanaUniCredit2020-04-28T13:22:57ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643587#M192111
<P>Also, when I run the proposed solution, it looks like it is never ending loop.</P>Tue, 28 Apr 2020 13:11:32 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643587#M192111JovanaUniCredit2020-04-28T13:11:32ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643583#M192109
<PRE>1 The SAS System Tuesday, April 28, 2020 12:45:00 PM
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='\\srvnas\common\CRO\Strategic Risk\Credit risk Governance and Models\Credit Risk Modeling\IFRS
5 ! 9\03 IFRS 9 Modeling\02 IFRS 9 TL modeling\QE & TL 2020\Transfer logic\IFRS 9 2020 TL v8.egp';
6 %LET _CLIENTPROJECTPATHHOST='UCB29756';
7 %LET _CLIENTPROJECTNAME='IFRS 9 2020 TL v8.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "F:\WORK\_TD4820_SRVSAS06_\Prc2/"
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 options mprint symbolgen mlogic;
27 options compress=binary;
28
29
30
31 DATA QntRegTest;
32 SET QntRegCorp /*QntRegCorpNonRev*/ /*QntRegCorpREV*/;
33
34 /*ISTA JE PODELA KOD SVA 3 segmenta*/
35 IF RatingClassFirstAv in (1,2,3,4,5) THEN I1 = 1;
36 ELSE I1 = 0;
37 IF RatingClassFirstAv in (6,7,8) THEN I2 = 1;
38 ELSE I2 = 0;
39
40 LnAge1 = LnAge*I1;
41 LnAge2 = LnAge*I2;
42
43 RUN;
NOTE: There were 26168 observations read from the data set WORK.QNTREGCORP.
NOTE: The data set WORK.QNTREGTEST has 26168 observations and 79 variables.
NOTE: Compressing data set WORK.QNTREGTEST decreased size by 83.41 percent.
Compressed is 106 pages; un-compressed would require 639 pages.
NOTE: DATA statement used (Total process time):
real time 0.25 seconds
cpu time 0.25 seconds
44
45 PROC QUANTREG DATA=QntRegTest OUTest=QntRegEst algorithm=interior (tolerance=1e-5) ci=resampling;
2 The SAS System Tuesday, April 28, 2020 12:45:00 PM
46 MODELLnAge12WORM: MODEL AP1 = LnPD0 LnAge1 LnAge2 / quantile=0.9 /*plots=all*/;
47 /*testAll: test LnPD0 LnAge1 LnAge2/ wald lr*/;
48 OUTPUT OUT=QntRegLnAge12 p=predicted;
49 title 'Quantile regression';
50 RUN;
NOTE: The data set WORK.QNTREGEST has 1 observations and 10 variables.
NOTE: Compressing data set WORK.QNTREGEST increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: The data set WORK.QNTREGLNAGE12 has 26168 observations and 81 variables.
NOTE: Compressing data set WORK.QNTREGLNAGE12 decreased size by 82.79 percent.
Compressed is 110 pages; un-compressed would require 639 pages.
NOTE: PROCEDURE QUANTREG used (Total process time):
real time 1.15 seconds
cpu time 1.15 seconds
51
52 DATA check;
53 SET QntRegLnAge12;
54 IF AP1 > predicted then Stage = 2; else Stage = 1;
55 dummy = 1;
56 RUN;
NOTE: There were 26168 observations read from the data set WORK.QNTREGLNAGE12.
NOTE: The data set WORK.CHECK has 26168 observations and 83 variables.
NOTE: Compressing data set WORK.CHECK decreased size by 83.05 percent.
Compressed is 111 pages; un-compressed would require 655 pages.
NOTE: DATA statement used (Total process time):
real time 0.25 seconds
cpu time 0.25 seconds
57
58 DATA QRParameters;
59 SET check;
60
61 Intercept = -0.6436; /*PROMENA*/
62 Coeff_LnPD0 = -0.2536; /*PROMENA*/
63 Coeff_LnAge1 = 0.6548; /*PROMENA*/
64 Coeff_LnAge2 = 0.2851; /*PROMENA*/
65 ALPHA =
65 ! SQRT((exp(Intercept+Coeff_LnPD0*log(0.005985769))*0.005985769)*(exp(Intercept+Coeff_LnPD0*log(0.023412558))*0.023412558))
65 ! ;
66 RUN;
NOTE: There were 26168 observations read from the data set WORK.CHECK.
NOTE: The data set WORK.QRPARAMETERS has 26168 observations and 88 variables.
NOTE: Compressing data set WORK.QRPARAMETERS decreased size by 81.53 percent.
Compressed is 121 pages; un-compressed would require 655 pages.
NOTE: DATA statement used (Total process time):
real time 0.26 seconds
cpu time 0.26 seconds
67
68 DATA beta_min_max;
69 SET QRParameters;
3 The SAS System Tuesday, April 28, 2020 12:45:00 PM
70
71 IF (Coeff_LnAge1 > 0 AND Coeff_LnAge2 > 0) THEN DO;
72 MIN = 0;
73 MAX = (1- ALPHA)/LOG(50);
74 END;
75 ELSE IF (Coeff_LnAge1 < 0 AND Coeff_LnAge2 < 0) THEN MAX = 0 AND MIN = -1*(ALPHA)/LOG(50);
76 RUN;
NOTE: There were 26168 observations read from the data set WORK.QRPARAMETERS.
NOTE: The data set WORK.BETA_MIN_MAX has 26168 observations and 90 variables.
NOTE: Compressing data set WORK.BETA_MIN_MAX decreased size by 81.38 percent.
Compressed is 119 pages; un-compressed would require 639 pages.
NOTE: DATA statement used (Total process time):
real time 0.26 seconds
cpu time 0.26 seconds
77
78 DATA realized_thresholds/*realized_thresholds_RCG1 realized_thresholds_RCG2*/;
79 SET beta_min_max (KEEP= LnPD0 PDPuncFirstAv RatingClassFirstAV RatingFirstAV RatingCurr RefDate Age ClientID ClientName
79 ! TransID SegmentFinal I1 I2 predicted PDPuncFirstAv PDPuncCurr Intercept Coeff_LnPD0 Coeff_LnAge1 Coeff_LnAge2 ALPHA MIN
79 ! MAX LnAge1 LnAge2);
80
81 PDThreshold = EXP(predicted)*PDPuncFirstAv; /* Predicted PD at reporting date (i.e. PD threshold)*/
82 IF PDThreshold >= 0.99 THEN PDThreshold = 0.99;
83 /*PDPuncCurr- OBSERVED PD at reporting date*/
84 IF (PDPuncCurr > PDThreshold) THEN StgChange = 1; /* boolean vector which indicates stage 2 obs. - 1's are in stage 2,
84 ! 0's in stage 1 */
85 ELSE StgChange = 0;
86
87 *IF I1 = 1 THEN OUTPUT realized_thresholds_RCG1;
88 *ELSE OUTPUT realized_thresholds_RCG2;
89 RUN;
NOTE: There were 26168 observations read from the data set WORK.BETA_MIN_MAX.
NOTE: The data set WORK.REALIZED_THRESHOLDS has 26168 observations and 26 variables.
NOTE: Compressing data set WORK.REALIZED_THRESHOLDS decreased size by 89.51 percent.
Compressed is 67 pages; un-compressed would require 639 pages.
NOTE: DATA statement used (Total process time):
real time 0.18 seconds
cpu time 0.17 seconds
90
91
92 PROC SQL;
93 CREATE TABLE obs_stg_change AS
94 SELECT t1.*, sum(t1.StgChange) as sum_StgChange,
95 count(t1.TransID) as no_obs,
96 (SUM(t1.StgChange))/count(t1.TransID) as QUANTILE,
97 (1-(SUM(t1.StgChange))/count(t1.TransID)) AS quantile_or
98
99 FROM realized_thresholds /*realized_thresholds_RCG1 realized_thresholds_RCG2*/ t1;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Compressing data set WORK.OBS_STG_CHANGE decreased size by 88.55 percent.
Compressed is 75 pages; un-compressed would require 655 pages.
NOTE: Table WORK.OBS_STG_CHANGE created, with 26168 rows and 30 columns.
4 The SAS System Tuesday, April 28, 2020 12:45:00 PM
100
101 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.21 seconds
cpu time 0.21 seconds
102
103
104 %macro makro(korak,broj);
105 DATA CTAge;
106 SET obs_stg_change;
107 step=((1-alpha)/log(50))/(50-1);
108 y= alpha+ &korak*log(Age);
109 IF(I1=1) THEN min_Threshold = min(y,PDThreshold);
110 IF(I2=1) THEN max_Threshold = max(y,PDThreshold);
111 min_max_Threshold=COALESCE(min_Threshold, max_Threshold);
112 IF PDPuncCurr > min_max_Threshold THEN StgChange_CT = 1; ELSE StgChange_CT = 0;
113 RUN;
114
115 /*0.5757232999 SUM SQUARE*/
116
117 PROC SQL;
118 CREATE TABLE CTAge_obs&broj AS
119 SELECT &korak AS step, min(t1.sum_StgChange) as sum_StgChange,
120 (SUM(t1.StgChange_CT)) AS SUM_StgChange_CT, /*a1*//* count # of obs which change stage */
121 /* (SUM(t1.StgChange_CT) - t1.sum_StgChange) AS PROVERA,*/
122 /* (SUM(abs(t1.StgChange_CT-t1.StgChange))) AS SUM_StgChange_DIFF,*/ /*Calculate distance between old CT and
122 ! new one */
123 (SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS SSQ2, /*sum of square*/
124 SQRT(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS dist_prov, /* square root of the distance */
125 ABS(SUM(t1.StgChange_CT) - min(t1.sum_StgChange)) AS err_stage2, /* Sum of obs which change stage */
126 ABS(SUM(t1.StgChange_CT)-sum(t1.StgChange))/MIN(t1.no_obs) AS ee
127 FROM WORK.CTAge t1;
128 QUIT;
129 %mend;
130
131 %macro sqlloop(start,end,step,pocetak,kraj);
132 PROC SQL;
133 %DO i=&start. %TO &end. %BY &step.;
134 %DO j=&pocetak. %TO &kraj.;
135 %makro(i,j);
136 %END;
137 %END;
138 QUIT;
139 %mend;
140
141 GOPTIONS NOACCESSIBLE;
142 %LET _CLIENTTASKLABEL=;
143 %LET _CLIENTPROCESSFLOWNAME=;
144 %LET _CLIENTPROJECTPATH=;
145 %LET _CLIENTPROJECTPATHHOST=;
146 %LET _CLIENTPROJECTNAME=;
147 %LET _SASPROGRAMFILE=;
148 %LET _SASPROGRAMFILEHOST=;
149
150 ;*';*";*/;quit;run;
151 ODS _ALL_ CLOSE;
5 The SAS System Tuesday, April 28, 2020 12:45:00 PM
152
153
154 QUIT; RUN;
155 </PRE>Tue, 28 Apr 2020 12:59:53 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643583#M192109JovanaUniCredit2020-04-28T12:59:53ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643547#M192089
Thank you, this helps a lot!<BR /><BR />A what about my first macro?<BR /><BR />I wrote this :<BR />%macro makro(korak,broj);<BR />DATA CTAge;<BR />SET obs_stg_change;<BR />step=((1-alpha)/log(50))/(50-1);<BR />y= alpha+ &korak*log(Age);<BR />IF(I1=1) THEN min_Threshold = min(y,PDThreshold);<BR />IF(I2=1) THEN max_Threshold = max(y,PDThreshold);<BR />min_max_Threshold=COALESCE(min_Threshold, max_Threshold);<BR />IF PDPuncCurr > min_max_Threshold THEN StgChange_CT = 1; ELSE StgChange_CT = 0;<BR />RUN;<BR /><BR />/*0.5757232999 SUM SQUARE*/<BR /><BR />PROC SQL;<BR />CREATE TABLE CTAge_obs&broj AS<BR />SELECT &korak AS step, min(t1.sum_StgChange) as sum_StgChange,<BR />(SUM(t1.StgChange_CT)) AS SUM_StgChange_CT, /*a1*//* count # of obs which change stage */<BR />/* (SUM(t1.StgChange_CT) - t1.sum_StgChange) AS PROVERA,*/<BR />/* (SUM(abs(t1.StgChange_CT-t1.StgChange))) AS SUM_StgChange_DIFF,*/ /*Calculate distance between old CT and new one */<BR />(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS SSQ2, /*sum of square*/<BR />SQRT(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS dist_prov, /* square root of the distance */<BR />ABS(SUM(t1.StgChange_CT) - min(t1.sum_StgChange)) AS err_stage2, /* Sum of obs which change stage */<BR />ABS(SUM(t1.StgChange_CT)-sum(t1.StgChange))/MIN(t1.no_obs) AS ee<BR />FROM WORK.CTAge t1;<BR />QUIT;<BR />%mend;<BR />%makro(0,1);<BR />%makro(0.001,2);<BR />%makro(0.0011,3);<BR />%makro(0.0012,4);<BR />%makro(0.0013,5);<BR />%makro(0.0014,6);<BR />...<BR /><BR />just like the statement from previous my comment, more that 200 times- that is the number of my steps, that I have to calculate, and I wanted to shorten it with:<BR />%macro sqlloop(start,end,step,pocetak,kraj);<BR />PROC SQL;<BR />%DO i=&start. %TO &end. %BY &step.;<BR />%DO j=&pocetak. %TO &kraj.;<BR />%makro(i,j);<BR />%END;<BR />%END;<BR />QUIT;<BR />%mend;<BR /><BR />%sqlloop(start=0, end=0.2507245866,step = 0.001,pocetak=1, kraj=242)<BR /><BR />But it doesn't work. The error was:<BR />ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:<BR />&end.<BR />ERROR: The %TO value of the %DO I loop is invalid.<BR />ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:<BR />&step.<BR />ERROR: The %BY value of the %DO I loop is invalid.<BR />ERROR: The macro SQLLOOP will stop executing.<BR /><BR />So, I concluded that there is problem with decimal numbers in macro.<BR /><BR />Am I right?Tue, 28 Apr 2020 11:37:53 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643547#M192089JovanaUniCredit2020-04-28T11:37:53ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643541#M192084
<P>Does it mean that my macro cannot work with decimal numbers?</P><P> </P><P>SInce arguments for my macro are decimal numbers.</P>Tue, 28 Apr 2020 11:31:05 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643541#M192084JovanaUniCredit2020-04-28T11:31:05ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643539#M192082
<P>This is the output of the log:</P><P> </P><P>1 The SAS System Tuesday, April 28, 2020 12:45:00 PM</P><P>1 ;*';*";*/;quit;run;<BR />2 OPTIONS PAGENO=MIN;<BR />3 %LET _CLIENTTASKLABEL='Program';<BR />4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';<BR />5 %LET _CLIENTPROJECTPATH='\\srvnas\common\CRO\Strategic Risk\Credit risk Governance and Models\Credit Risk Modeling\IFRS<BR />5 ! 9\03 IFRS 9 Modeling\02 IFRS 9 TL modeling\QE & TL 2020\Transfer logic\IFRS 9 2020 TL v8.egp';<BR />6 %LET _CLIENTPROJECTPATHHOST='UCB29756';<BR />7 %LET _CLIENTPROJECTNAME='IFRS 9 2020 TL v8.egp';<BR />8 %LET _SASPROGRAMFILE='';<BR />9 %LET _SASPROGRAMFILEHOST='';<BR />10<BR />11 ODS _ALL_ CLOSE;<BR />12 OPTIONS DEV=PNG;<BR />13 GOPTIONS XPIXELS=0 YPIXELS=0;<BR />14 FILENAME EGSR TEMP;<BR />15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR<BR />16 STYLE=HtmlBlue<BR />17 STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")<BR />18 NOGTITLE<BR />19 NOGFOOTNOTE<BR />20 GPATH=&sasworklocation<BR />SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "F:\WORK\_TD4820_SRVSAS06_\Prc2/"<BR />21 ENCODING=UTF8<BR />22 options(rolap="on")<BR />23 ;<BR />NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR<BR />24<BR />25 GOPTIONS ACCESSIBLE;<BR />26 options mprint symbolgen mlogic;<BR />27 options compress=binary;<BR />28<BR />29<BR />30<BR />31 DATA QntRegTest;<BR />32 SET QntRegCorp /*QntRegCorpNonRev*/ /*QntRegCorpREV*/;<BR />33<BR />34 /*ISTA JE PODELA KOD SVA 3 segmenta*/<BR />35 IF RatingClassFirstAv in (1,2,3,4,5) THEN I1 = 1;<BR />36 ELSE I1 = 0;<BR />37 IF RatingClassFirstAv in (6,7,8) THEN I2 = 1;<BR />38 ELSE I2 = 0;<BR />39<BR />40 LnAge1 = LnAge*I1;<BR />41 LnAge2 = LnAge*I2;<BR />42<BR />43 RUN;</P><P>NOTE: There were 26168 observations read from the data set WORK.QNTREGCORP.<BR />NOTE: The data set WORK.QNTREGTEST has 26168 observations and 79 variables.<BR />NOTE: Compressing data set WORK.QNTREGTEST decreased size by 83.41 percent.<BR />Compressed is 106 pages; un-compressed would require 639 pages.<BR />NOTE: DATA statement used (Total process time):<BR />real time 0.32 seconds<BR />cpu time 0.32 seconds</P><P>44<BR />45 PROC QUANTREG DATA=QntRegTest OUTest=QntRegEst algorithm=interior (tolerance=1e-5) ci=resampling;<BR />2 The SAS System Tuesday, April 28, 2020 12:45:00 PM</P><P>46 MODELLnAge12WORM: MODEL AP1 = LnPD0 LnAge1 LnAge2 / quantile=0.9 /*plots=all*/;<BR />47 /*testAll: test LnPD0 LnAge1 LnAge2/ wald lr*/;<BR />48 OUTPUT OUT=QntRegLnAge12 p=predicted;<BR />49 title 'Quantile regression';<BR />50 RUN;</P><P>NOTE: The data set WORK.QNTREGEST has 1 observations and 10 variables.<BR />NOTE: Compressing data set WORK.QNTREGEST increased size by 100.00 percent.<BR />Compressed is 2 pages; un-compressed would require 1 pages.<BR />NOTE: The data set WORK.QNTREGLNAGE12 has 26168 observations and 81 variables.<BR />NOTE: Compressing data set WORK.QNTREGLNAGE12 decreased size by 82.79 percent.<BR />Compressed is 110 pages; un-compressed would require 639 pages.<BR />NOTE: PROCEDURE QUANTREG used (Total process time):<BR />real time 1.12 seconds<BR />cpu time 1.15 seconds</P><P>51<BR />52 DATA check;<BR />53 SET QntRegLnAge12;<BR />54 IF AP1 > predicted then Stage = 2; else Stage = 1;<BR />55 dummy = 1;<BR />56 RUN;</P><P>NOTE: There were 26168 observations read from the data set WORK.QNTREGLNAGE12.<BR />NOTE: The data set WORK.CHECK has 26168 observations and 83 variables.<BR />NOTE: Compressing data set WORK.CHECK decreased size by 83.05 percent.<BR />Compressed is 111 pages; un-compressed would require 655 pages.<BR />NOTE: DATA statement used (Total process time):<BR />real time 0.23 seconds<BR />cpu time 0.23 seconds</P><P>57<BR />58 DATA QRParameters;<BR />59 SET check;<BR />60<BR />61 Intercept = -0.6436; /*PROMENA*/<BR />62 Coeff_LnPD0 = -0.2536; /*PROMENA*/<BR />63 Coeff_LnAge1 = 0.6548; /*PROMENA*/<BR />64 Coeff_LnAge2 = 0.2851; /*PROMENA*/<BR />65 ALPHA =<BR />65 ! SQRT((exp(Intercept+Coeff_LnPD0*log(0.005985769))*0.005985769)*(exp(Intercept+Coeff_LnPD0*log(0.023412558))*0.023412558))<BR />65 ! ;<BR />66 RUN;</P><P>NOTE: There were 26168 observations read from the data set WORK.CHECK.<BR />NOTE: The data set WORK.QRPARAMETERS has 26168 observations and 88 variables.<BR />NOTE: Compressing data set WORK.QRPARAMETERS decreased size by 81.53 percent.<BR />Compressed is 121 pages; un-compressed would require 655 pages.<BR />NOTE: DATA statement used (Total process time):<BR />real time 0.29 seconds<BR />cpu time 0.29 seconds</P><P>67<BR />68 DATA beta_min_max;<BR />69 SET QRParameters;<BR />3 The SAS System Tuesday, April 28, 2020 12:45:00 PM</P><P>70<BR />71 IF (Coeff_LnAge1 > 0 AND Coeff_LnAge2 > 0) THEN DO;<BR />72 MIN = 0;<BR />73 MAX = (1- ALPHA)/LOG(50);<BR />74 END;<BR />75 ELSE IF (Coeff_LnAge1 < 0 AND Coeff_LnAge2 < 0) THEN MAX = 0 AND MIN = -1*(ALPHA)/LOG(50);<BR />76 RUN;</P><P>NOTE: There were 26168 observations read from the data set WORK.QRPARAMETERS.<BR />NOTE: The data set WORK.BETA_MIN_MAX has 26168 observations and 90 variables.<BR />NOTE: Compressing data set WORK.BETA_MIN_MAX decreased size by 81.38 percent.<BR />Compressed is 119 pages; un-compressed would require 639 pages.<BR />NOTE: DATA statement used (Total process time):<BR />real time 0.29 seconds<BR />cpu time 0.29 seconds</P><P>77<BR />78 DATA realized_thresholds/*realized_thresholds_RCG1 realized_thresholds_RCG2*/;<BR />79 SET beta_min_max (KEEP= LnPD0 PDPuncFirstAv RatingClassFirstAV RatingFirstAV RatingCurr RefDate Age ClientID ClientName<BR />79 ! TransID SegmentFinal I1 I2 predicted PDPuncFirstAv PDPuncCurr Intercept Coeff_LnPD0 Coeff_LnAge1 Coeff_LnAge2 ALPHA MIN<BR />79 ! MAX LnAge1 LnAge2);<BR />80<BR />81 PDThreshold = EXP(predicted)*PDPuncFirstAv; /* Predicted PD at reporting date (i.e. PD threshold)*/<BR />82 IF PDThreshold >= 0.99 THEN PDThreshold = 0.99;<BR />83 /*PDPuncCurr- OBSERVED PD at reporting date*/<BR />84 IF (PDPuncCurr > PDThreshold) THEN StgChange = 1; /* boolean vector which indicates stage 2 obs. - 1's are in stage 2,<BR />84 ! 0's in stage 1 */<BR />85 ELSE StgChange = 0;<BR />86<BR />87 *IF I1 = 1 THEN OUTPUT realized_thresholds_RCG1;<BR />88 *ELSE OUTPUT realized_thresholds_RCG2;<BR />89 RUN;</P><P>NOTE: There were 26168 observations read from the data set WORK.BETA_MIN_MAX.<BR />NOTE: The data set WORK.REALIZED_THRESHOLDS has 26168 observations and 26 variables.<BR />NOTE: Compressing data set WORK.REALIZED_THRESHOLDS decreased size by 89.51 percent.<BR />Compressed is 67 pages; un-compressed would require 639 pages.<BR />NOTE: DATA statement used (Total process time):<BR />real time 0.18 seconds<BR />cpu time 0.18 seconds</P><P>90<BR />91<BR />92 PROC SQL;<BR />93 CREATE TABLE obs_stg_change AS<BR />94 SELECT t1.*, sum(t1.StgChange) as sum_StgChange,<BR />95 count(t1.TransID) as no_obs,<BR />96 (SUM(t1.StgChange))/count(t1.TransID) as QUANTILE,<BR />97 (1-(SUM(t1.StgChange))/count(t1.TransID)) AS quantile_or<BR />98<BR />99 FROM realized_thresholds /*realized_thresholds_RCG1 realized_thresholds_RCG2*/ t1;<BR />NOTE: The query requires remerging summary statistics back with the original data.<BR />NOTE: Compressing data set WORK.OBS_STG_CHANGE decreased size by 88.55 percent.<BR />Compressed is 75 pages; un-compressed would require 655 pages.<BR />NOTE: Table WORK.OBS_STG_CHANGE created, with 26168 rows and 30 columns.</P><P>4 The SAS System Tuesday, April 28, 2020 12:45:00 PM</P><P>100<BR />101 QUIT;<BR />NOTE: PROCEDURE SQL used (Total process time):<BR />real time 0.21 seconds<BR />cpu time 0.21 seconds</P><P>102<BR />103<BR />104 %macro makro(korak,broj);<BR />105 DATA CTAge;<BR />106 SET obs_stg_change;<BR />107 step=((1-alpha)/log(50))/(50-1);<BR />108 y= alpha+ &korak*log(Age);<BR />109 IF(I1=1) THEN min_Threshold = min(y,PDThreshold);<BR />110 IF(I2=1) THEN max_Threshold = max(y,PDThreshold);<BR />111 min_max_Threshold=COALESCE(min_Threshold, max_Threshold);<BR />112 IF PDPuncCurr > min_max_Threshold THEN StgChange_CT = 1; ELSE StgChange_CT = 0;<BR />113 RUN;<BR />114<BR />115 /*0.5757232999 SUM SQUARE*/<BR />116<BR />117 PROC SQL;<BR />118 CREATE TABLE CTAge_obs&broj AS<BR />119 SELECT &korak AS step, min(t1.sum_StgChange) as sum_StgChange,<BR />120 (SUM(t1.StgChange_CT)) AS SUM_StgChange_CT, /*a1*//* count # of obs which change stage */<BR />121 /* (SUM(t1.StgChange_CT) - t1.sum_StgChange) AS PROVERA,*/<BR />122 /* (SUM(abs(t1.StgChange_CT-t1.StgChange))) AS SUM_StgChange_DIFF,*/ /*Calculate distance between old CT and<BR />122 ! new one */<BR />123 (SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS SSQ2, /*sum of square*/<BR />124 SQRT(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS dist_prov, /* square root of the distance */<BR />125 ABS(SUM(t1.StgChange_CT) - min(t1.sum_StgChange)) AS err_stage2, /* Sum of obs which change stage */<BR />126 ABS(SUM(t1.StgChange_CT)-sum(t1.StgChange))/MIN(t1.no_obs) AS ee<BR />127 FROM WORK.CTAge t1;<BR />128 QUIT;<BR />129 %mend;<BR />130<BR />131 %macro sqlloop(start,end,step,pocetak,kraj);<BR />132 PROC SQL;<BR />133 %DO i=&start. %TO &end. %BY &step.;<BR />134 %DO j=&pocetak. %TO &kraj.;<BR />135 %makro(i,j);<BR />136 %END;<BR />137 %END;<BR />138 QUIT;<BR />139 %mend;<BR />140<BR />141 GOPTIONS NOACCESSIBLE;<BR />142 %LET _CLIENTTASKLABEL=;<BR />143 %LET _CLIENTPROCESSFLOWNAME=;<BR />144 %LET _CLIENTPROJECTPATH=;<BR />145 %LET _CLIENTPROJECTPATHHOST=;<BR />146 %LET _CLIENTPROJECTNAME=;<BR />147 %LET _SASPROGRAMFILE=;<BR />148 %LET _SASPROGRAMFILEHOST=;<BR />149<BR />150 ;*';*";*/;quit;run;<BR />151 ODS _ALL_ CLOSE;<BR />5 The SAS System Tuesday, April 28, 2020 12:45:00 PM</P><P>152<BR />153<BR />154 QUIT; RUN;<BR />155</P><P> </P><P> </P><P>It is strange that now there is no error, but no tabels in output of the macro exists.</P>Tue, 28 Apr 2020 11:29:55 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643539#M192082JovanaUniCredit2020-04-28T11:29:55ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643531#M192076
<P>The export from my macro should be more than 200 tables with just one row. I want to join them, with all rows in one table one below another.</P><P>I did this like this:</P><P>PROC SQL;<BR />CREATE TABLE bete as<BR />SELECT * FROM CTAge_obs1<BR />UNION SELECT * FROM CTAge_obs2<BR />UNION SELECT * FROM CTAge_obs3<BR />UNION SELECT * FROM CTAge_obs4<BR />UNION SELECT * FROM CTAge_obs5<BR />UNION SELECT * FROM CTAge_obs6<BR />UNION SELECT * FROM CTAge_obs7<BR />UNION SELECT * FROM CTAge_obs8<BR />UNION SELECT * FROM CTAge_obs9<BR />UNION SELECT * FROM CTAge_obs10</P><P>...</P><P>until the last table.</P><P> </P><P>Can it be done on more sophisticated way? Not to write it more than 200 times?</P>Tue, 28 Apr 2020 11:22:49 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643531#M192076JovanaUniCredit2020-04-28T11:22:49ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643529#M192074
<P>Should I run whole program or just that line that you wrote me?</P>Tue, 28 Apr 2020 11:20:42 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643529#M192074JovanaUniCredit2020-04-28T11:20:42ZRe: Macro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643526#M192071
<P>...And what is proposal hot to join all tables made in macro? Since, those tables contain just one row, I want them one below another in one table.</P>Tue, 28 Apr 2020 11:17:05 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643526#M192071JovanaUniCredit2020-04-28T11:17:05ZMacro errors
https://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643524#M192069
<P>I am trying to execute this code/macro, but there is some error:</P><P> </P><P>%macro sqlloop(start,end,step,pocetak,kraj);<BR />PROC SQL;<BR />%DO i=&start. %TO &end. %BY &step.;<BR />%DO j=&pocetak. %TO &kraj.;<BR />%makro(i,j);<BR />%END;<BR />%END;<BR />QUIT;<BR />%mend;</P><P>%sqlloop(start=0, end=0.2507245866,step = 0.001,pocetak=1, kraj=242)</P><P> </P><P> </P><P>ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:<BR />&end.<BR />ERROR: The %TO value of the %DO I loop is invalid.<BR />ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:<BR />&step.<BR />ERROR: The %BY value of the %DO I loop is invalid.<BR />ERROR: The macro SQLLOOP will stop executing.</P><P> </P><P>Is it possible fro macro to work with decimal numbers? Or something else is problem?</P>Tue, 28 Apr 2020 11:13:21 GMThttps://communities.sas.com/t5/SAS-Programming/Macro-errors/m-p/643524#M192069JovanaUniCredit2020-04-28T11:13:21ZERROR 180-322: Statement is not valid or it is used out of proper order.
https://communities.sas.com/t5/SAS-Programming/ERROR-180-322-Statement-is-not-valid-or-it-is-used-out-of-proper/m-p/642537#M191702
<P>When I want to run this macro:</P><P> </P><P>%macro prep_graph(Tab_Input=,stat=,var=);<BR />proc transpose data=&Tab_Input out=&stat._&var._PRE(drop=_name_);<BR />by RatingClassFirstAv;<BR />id Age;<BR />var &stat._&var.;<BR />run;</P><P>proc transpose data=&stat._&var._PRE out=&stat._&var._graph<BR />(rename=(_NAME_ = Age));<BR />id RatingClassFirstAv;<BR />run;</P><P>proc sql;<BR />create table &stat._&var._graph as<BR />select input(Age, 4.) as Age, *<BR />from &stat._&var._graph ;<BR />quit;</P><P>proc sort data=&stat._&var._graph ;<BR />by Age;<BR />run;</P><P>proc transpose data=&stat._&var._graph out=&stat._&var._PRE (rename=(_NAME_ = RatingClassFirstAv));<BR />id Age;<BR />run;<BR />%mend prep_graph;</P><P>%prep_graph(Tab_Input=Stat, stat=Avg, ,var=PD);</P><P> </P><P>I've got an message:</P><P> </P><P>WARNING: Apparent invocation of macro PREP_GRAPH not resolved.</P><P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P><P> </P><P>And when I run everything without macro, it works perfectly.</P><P> </P><P>How to resolve it?</P>Fri, 24 Apr 2020 11:51:10 GMThttps://communities.sas.com/t5/SAS-Programming/ERROR-180-322-Statement-is-not-valid-or-it-is-used-out-of-proper/m-p/642537#M191702JovanaUniCredit2020-04-24T11:51:10Z