BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kurt_Bremser
Super User

@JovanaUniCredit wrote:

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.

 


This is patently wrong:

%macro makro(i,j);
%put &=i,&=j;
%mend;

data _null_;
call execute('%nrstr(%makro(0,1))');
i = .001;
do j = 2 to 242;
  call execute(cats('%nrstr(%makro(',i,',',j,'))'));
  i = i + .0001;
end;
run;

Log:

73         %macro makro(i,j);
 74         %put&=i,&=j;
 75         %mend;
 76         
 77         data _null_;
 78         call execute('%nrstr(%makro(0,1))');
 79         i = .001;
 80         do j = 2 to 242;
 81           call execute(cats('%nrstr(%makro(',i,',',j,'))'));
 82           i = i + .0001;
 83         end;
 84         run;
 
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.01 seconds
       cpu time            0.00 seconds
       
 
 NOTE: CALL EXECUTE generated line.
 1         + %makro(0,1)
 I=0,J=1
 2         + %makro(0.001,2)
 I=0.001,J=2
 3         + %makro(0.0011,3)
 I=0.0011,J=3
 4         + %makro(0.0012,4)
 I=0.0012,J=4
 5         + %makro(0.0013,5)
 I=0.0013,J=5
 6         + %makro(0.0014,6)
 I=0.0014,J=6
 7         + %makro(0.0015,7)
 I=0.0015,J=7

(snipped)


 240       + %makro(0.0248,240)
 I=0.0248,J=240
 241       + %makro(0.0249,241)
 I=0.0249,J=241
 242       + %makro(0.025,242)
 I=0.025,J=242
JovanaUniCredit
Calcite | Level 5

Great!

 

Last but not the least, when this is finished I don't have any output.

 

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:

 

data bete;
set ctage_obs:;
run;

 

But it won't work, unfortinately.

 

What is the problem?

 

This is the code I run:

 

%macro makro(korak,broj);
DATA CTAge;
SET obs_stg_change;
step=((1-alpha)/log(50))/(50-1);
y= alpha+ &korak*log(Age);
IF(I1=1) THEN min_Threshold = min(y,PDThreshold);
IF(I2=1) THEN max_Threshold = max(y,PDThreshold);
min_max_Threshold=COALESCE(min_Threshold, max_Threshold);
IF PDPuncCurr > min_max_Threshold THEN StgChange_CT = 1; ELSE StgChange_CT = 0;
RUN;

PROC SQL;
CREATE TABLE CTAge_obs&broj AS
SELECT &korak AS step, min(t1.sum_StgChange) as sum_StgChange,
(SUM(t1.StgChange_CT)) AS SUM_StgChange_CT, /*a1*//* count # of obs which change stage */
/* (SUM(t1.StgChange_CT) - t1.sum_StgChange) AS PROVERA,*/
/* (SUM(abs(t1.StgChange_CT-t1.StgChange))) AS SUM_StgChange_DIFF,*/ /*Calculate distance between old CT and new one */
(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS SSQ2, /*sum of square*/
SQRT(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS dist_prov, /* square root of the distance */
ABS(SUM(t1.StgChange_CT) - min(t1.sum_StgChange)) AS err_stage2, /* Sum of obs which change stage */
ABS(SUM(t1.StgChange_CT)-sum(t1.StgChange))/MIN(t1.no_obs) AS ee
FROM WORK.CTAge t1;
QUIT;
%mend;

 

%macro makro(i,j);
%put &=i,&=j;
%mend;

data _null_;
call execute('%nrstr(%makro(0,1))');
i = .001;
do j = 2 to 242;
call execute(cats('%nrstr(%makro(',i,',',j,'))'));
i = i + .0001;
end;
run;

data bete;
set ctage_obs:;
run;

 

And I can see error in my log:

 

ERROR: The data set list (WORK.ctage_obs:) does not contain any members.

Kurt_Bremser
Super User

The macro (%makro) I used in my code was for simple demonstration purposes to show you that the loop works and calls the macro correctly. Since I used the same name as your macro, my macro code redefines yours, so nothing aside from the %put happens. Remove my 3-line macro code.

JovanaUniCredit
Calcite | Level 5
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!

It works now!

Finally, I can see the table like this:
step sum_StgChange SUM_StgChange_CT SSQ2 dist_prov err_stage2 ee
0 2608 2618 0.57534472 0.758514812 10 0.000382146
0.0018 2608 2587 0.509350616 0.713688038 -21 0.000802507
0.0108 2608 2511 0.347611235 0.589585647 -97 0.003706818
0.0109 2608 2511 0.346960105 0.589033195 -97 0.003706818

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.

I tried to do it with this code:

%LET distance = 1E6;
%LET err_prev = 1E6;
%LET new_quantile = 0;
%LET change_quant = 0;
%LET beta = 0;

DATA BETA_FINAL;
SET bete;

err_prev = 1E6;
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;
END;
RUN;

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.

Do you have any suggestions how to do this?

Thank you!

Kurt_Bremser
Super User

First of all: use the "little running man" EVERY TIME you post code. ALWAYS. NEVER post code in the main posting window. NOT before hell freezes over so much that the brimstone becomes super-conducting.

 

Next, be consistent in writing your code, don't shout at us and the SAS interpreter, use proper indentation and whitespace to make elements visible. Don't use brackets where none are needed, that causes unnecessary confusion. You may use them at places where you want to clarify your intents (as in "I know what I'm doing here").

So your code should look like

data beta_final;
set bete;
err_prev = 1E6;
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;
end;
run;

(putting "then do;" on its own line is a personal preference of mine; do that as you like)

Which of the codes is easier to read and understand?

 

What defines your "most optimal step"? Create a variable, and the select for the maximum or minimum, like

proc sql;
create table want as
  select *
  from have
  having target = max(target)
;
quit;
JovanaUniCredit
Calcite | Level 5
Well, my final step that I will choose for solution of my problem is the step which meet next conditions:
ee < 0.01;
err_stage2 is minimal for that step;
prov _prov is minimal for that step;

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.

That was my idea.
Kurt_Bremser
Super User

I see that we need to work on the choice of words.

A dataset (or "table") consists of observations (called "rows" in other environments)

Observations contain variables (often called "columns" in other environments)

A multiple of observations can make up groups; you can have several layers of groupings within a dataset

 

Within these context, where would you put "step"?

 

To solve your question, it would be very helpful to have data and the expected result from it.

To post example data in a usable way, create a data step with datalines and post it as previously mentioned.

JovanaUniCredit
Calcite | Level 5
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;

This is my data step.

 

Expected result: step = 0.0117

 

Is it ok like this?

 

I am filing like learning it.

Kurt_Bremser
Super User

OK, so "step" is a variable used to identify an observation.

 

Since all your values seem to be numeric, you should read them as such; this allows use of numeric functions like min():

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
;

proc sql;
select * from step (where=(ee < .01))
having dist_prov = min(dist_prov);
quit;

I deliberately only tested for one column, as it is not clear what should happen if observation X meets condition one, and observation Y meets condition two.

 

JovanaUniCredit
Calcite | Level 5
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;

But, what if I have this data. It won't work.

 

Therefore, I think I have to add this kind of code/condition:

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;

 

However, sill don't know how to create code that result in one observation.

 

Kurt_Bremser
Super User

My code results in exactly one observation, proof:

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
;

proc sql;
select * from step (where=(ee < .01))
having dist_prov = min(dist_prov);
quit;

Result:

step	dist_prov	err_stage2	ee
0.0141	0.580433	-113	0.004318
JovanaUniCredit
Calcite | Level 5

 But that is not correct result!

 

My result should be the result in which is all three conditions fulfilled:

ee < 0.01

err_stage2 is minimal

dist_prov is minimal

 

Therefore I need to put all three conditions.

 

In the first step:

distance and err_prev should be set to 1E6;

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.

 

This shoul be running until non of conditions are met, and the code should return previous observation.

Kurt_Bremser
Super User

@JovanaUniCredit wrote:

 But that is not correct result!

 

My result should be the result in which is all three conditions fulfilled:

ee < 0.01

err_stage2 is minimal

dist_prov is minimal

 


None of the observations in your dataset as posted fulfill all three conditions, just run

 

proc sql;
select * from step (where=(ee < .01))
having dist_prov = min(dist_prov) and err_stage2 = min(err_stage2);
quit;

for proof.

 

 

And I have absolutely no idea wht you mean by that:

In the first step:

distance and err_prev should be set to 1E6;

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.

 

This shoul be running until non of conditions are met, and the code should return previous observation

I see no value of 1E6 (1000000) anywhere, so you must be talking about completely different data.

 

PLEASE POST:

The data you have.

The data you want from it.

 

JovanaUniCredit
Calcite | Level 5
Never mind. I will think something up. I think that you don't understand what is my problem that I have to code it.

Anyway, thank you for help for previous problem.
Tom
Super User Tom
Super User

If you want to run N iterations and increment by a non-integer value on each iteration then just do the math yourself.

If you need the floating point number in a macro variable use %SYSEVALF().  

%let increment=0.001;
%do index=1 to 242 ;
  %let number=%sysevalf(&index * &increment);
  ...
%end;

But if you don't need it in a macro variable then just let the generated SAS code compute the number for you.

data run&index;
    number = &index * &increment;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 38 replies
  • 1495 views
  • 1 like
  • 6 in conversation