About ertr

ertr · ‎08-01-2016

Hello everyone, In addition to my following discussion, I would like to ask some additional questions to you. https://communities.sas.com/t5/Base-SAS-Programming/Dynamic-Macro-Loop-To-Create-An-Automatic-Loop/m-p/280613#M56729 Thanks to @Tom and @Reeza I could do the foregoing task. Now, I have a little bit harder task to do it. This time I have two data sets and the secondary data set will be affected by the primary data set. Have1 is my first data set and Have2 is my secondary data set. We found the count of Period by writing starting and ending dates. Critical point is that we count every three months as one Period starting from the end date. @Tom's code can do this properly. My question is related to adjust the Secondary(Have2) data set for first(Have1) data set.Which range period of Have1 data set is match with the first month of Have2 data set, then that number of period will be same with first month of Have2 data set. It can come to you a little bit complicated. To be more precise, I added an image the bottom of my question. Data Have; Format Pro_End_Date_Ym DATE9.; Infile Datalines Missover; Input Pro_End_Date_Ym; Datalines; 10200 10300 10400 10500 10600 10700 10800 10900 10870 10880 10991 10999 11100 11150 11250 ; Run; Data Have2; Format Pro_End_Date_Ym DATE9.; Infile Datalines Missover; Input Pro_End_Date_Ym; Datalines; 10880 10991 10999 11100 11150 11250 ; Run; %Let startdate=01DEC1987; %let enddate=31OCT1990; %let startdate2 = %sysfunc(intnx(month,"&startdate"d,0,b))-1; %let startdate2 = %sysfunc(putn(&startdate2,date9)); %let nperiods=%sysfunc(ceil(%sysfunc(intck(month,"&startdate2"d,"&enddate"d))/3)); %put &=nperiods; data want; set have; if Pro_End_Date_Ym < "&startdate"d or Pro_End_Date_Ym > "&enddate"d then period=0; else period = &nperiods - int(intck('month',Pro_End_Date_Ym,"&enddate"d)/3); run; Data want2; set have2; if Pro_End_Date_Ym < "&startdate"d or Pro_End_Date_Ym > "&enddate"d then period=0; else period = &nperiods - int(intck('month',Pro_End_Date_Ym,"&enddate"d)/3); run; Can anybody help me, please? Thank you

ertr · ‎07-18-2016

@Cynthia_sas, Thank you very much for detailed information. I can use your second method but my real purpose is that detect the"Main" value then order the "Datasets" data set. Here is the point of my ordering->"Main" will be the first the other values will remain same. I can understand that you don't understand what this piece of my program has to do with the screen shot of the output that my posted, but it is little bit complicated. I just wanted to ask is there any way to do my desired calculation. I just want to compare the one data set with other ones depending on "Main" value for my sample. As I said, I want to detect "Main" value then I want to order. But your second method seems fine, thank you very much for your warm approach. Data Datasets; Length DSname $42 DSlabel $ 25 Label $50 Internal_Ord 8; Infile Cards Dsd Dlm=" "; Input DSName $ DSLabel Label $ Internal_Ord; Cards; Have1 Comparing1 "Data Set1" 1.2 Have2 Main "Data Set2" 1.1 Have3 Comparing2 "Data Set3" 1.3 ; Run; Proc Sql; Create Table Datasets2 As Select * From Datasets Order By Internal_Ord; Quit; Proc Sql NoPrint; Select Upcase(DSname) Into: Dataset_Names separated by " " From Datasets2; Run; Proc Sql NoPrint; Select Upcase(DSlabel) Into: Suffixes separated by " " From Datasets2; Run; Proc Sql NoPrint; Select Label Into: Labels separated by "|" From Datasets2; Run; %put &Dataset_Names; %put &Suffixes; %put &Labels;

ertr · ‎07-18-2016

Okay @Cynthia_sas, I'm sorry for confusing you. Let's say I have a sample code as below.(I copied my previos sample); Data Datasets; Length DSname $42 DSlabel $ 25 Label $50; Infile Cards Dsd Dlm=" "; Input DSName $ DSLabel Label $; Cards; Have1 Comparing1 "Data Set1" Have2 Main "Data Set2" Have3 Comparing2 "Data Set3" ; Run; Proc Sql NoPrint; Select Upcase(DSname) Into: Dataset_Names separated by " " From Datasets; Run; Proc Sql NoPrint; Select Upcase(DSlabel) Into: Suffixes separated by " " From Datasets; Run; Proc Sql NoPrint; Select Label Into: Labels separated by "|" From Datasets; Run; %put &Dataset_Names; %put &Suffixes; %put &Labels; And these macro variables printing following output. But I want to change these values order depending on "MAIN" value, "MAIN" must be first, depending on "MAIN" its Data Set Name -> "Have2" and its Data Set Label -> "Data Set2" must also be first. The remaining values can ben in the same order. Here is my desired order;("MAIN" value is keyword) Can I tell my question much better? I hope I could, if I could not, I will re-try to explain my question. Thank you very much for sparing time for me 🙂

ertr · ‎07-15-2016

Can somebody help me, please? If you didn't understand my questions, I can re-explain my questions Thank you

ertr · ‎07-14-2016

@Cynthia_sas hello again, I re-prepared my code. I used the Have1, Have2 and Have3 data sets which I mentioned in my previous messages. Let's pretend, the user filled the "Datasets" data set as below. In my sample code comparing calculations are done in Loop_Test loop. As you can see, I compare the Have1 data set with Have2&Have3 data sets by using following code, however, I want to detect the "Main" value from "Datasets" data set and compare the Have2 data set with Have1&Have3 data sets. What I mean is that the code should depend on the "Main" value, if Have2 row has "Main" value so Have2 will be my compared data set. if instead of Have2, Have3 row has "Main" value so I will compare the Have1&Have2 data sets with Have3 data set. Data Datasets; Length DSname $42 DSlabel $ 25 Label $50; Infile Cards Dsd Dlm=" "; Input DSName $ DSLabel Label $; Cards; Have1 Comparing1 "Data Set1" Have2 Main "Data Set2" Have3 Comparing2 "Data Set3" ; Run; Proc Sql NoPrint; Select Upcase(DSname) Into: Dataset_Names separated by " " From Datasets; Run; Proc Sql NoPrint; Select Upcase(DSlabel) Into: Suffixes separated by " " From Datasets; Run; Proc Sql NoPrint; Select Label Into: Labels separated by "|" From Datasets; Run; %put &Dataset_Names; %put &Suffixes; %put &Labels; %Macro Test(DSConstant,DSName,DSLabel); PROC SQL; Create Table Comparision_&DSName. As Select T1.Variable ,T1.StdDev1 ,T2.StdDev1 As StdDev2 ,"&DSLabel." As Dataset From &DSConstant. T1 Left Join &DSName. T2 On T1.Variable=T2.Variable; QUIT; %Mend Test; %Macro Loop_Test; %let i=1; %do %While(%scan(&Dataset_Names.,&i.+1,%str( ))~=); %Test(%Scan(&Dataset_Names.,1,%str( )),%Scan(&Dataset_Names.,&i.+1,%str( )),%Scan(&Labels.,&i.+1,%str(|))); %let i=&i.+1; %end; %Mend Loop_Test; %Loop_Test; My question is related to how can I build these structure? The following first two data sets are the output of my code but I want to get the latest two output by depending on my foregoing explanations, is it possible? Thank you

ertr · ‎06-28-2016

Hello, I want to get following "Variable" column's first three digits. I define the first three digits i.e:"WOE", "REP". If I use "=:" option then I write first three digits, it brings my desired values but what if I have more than one value. Sometimes, I need 5 or 6 values i.e:"WOE","REP","IMP","ITA","BEL" etc. so I want to put these values into macro variable then use into my code. I hope I could make myself clear. Can you help me about to reach my aim, please? Data Have; Do i=1 to 5; Variable="WOE_Hart"; Output; End; Do i=1 to 5; Variable="REP_Buff"; Output; End; Do i=1 to 5; Variable="IMP_Steph"; Output; End; Run; Data Want2; Set Have; If Variable=:"REP" Or Variable =:"IMP" Or Variable=:"ASK "; Run; %Let Macro= "WOE" "REP"; Data Want3; Set Have; If Variable =:(&Macro.); Run; Thank you

ertr · ‎06-28-2016

Hello everyone, I posted the following code for another question (https://communities.sas.com/t5/SAS-Procedures/Proc-Logistic-How-to-Pull-Specific-Statistical-Values/m-p/278361#M58881) ,thanks to @Rick_SAS I solved that Discussion. In that discussion, I realized I'm getting some errors I just don't understand even though macro resolves Variable3 and Variable4 why it doesn't resolve the Variable1. Can someone help me that how can I build proper structure? If you execute the code you can see the errors in the log. Data Have; Length Target 8 Variable1 8 Variable3 8 Variable4 8 ; Infile Datalines Missover; Input Target Variable1 Variable3 Variable4 ; Datalines; 0 -0.435409602 0.1111702173 -0.375361259 0 -0.435409602 0.9767642068 -0.375361259 1 -0.435409602 0.9767642068 -0.375361259 0 -0.435409602 0.9767642068 -0.375361259 0 0.8432328141 0.9767642068 -0.566274869 1 -0.435409602 0.9767642068 0.5257570782 0 -0.202340531 0 0.5257570782 0 -0.435409602 0 0.5257570782 1 0.3610487663 -1.119484453 0.5257570782 1 0 0 0.1876969043 0 -0.435409602 -0.82913977 -0.566274869 0 -0.435409602 -0.82913977 0.1876969043 1 0.3610487663 0.9767642068 -0.199600942 0 0.8432328141 0 -0.199600942 0 -0.202340531 0.9767642068 -0.375361259 ; Run; %Macro Fake_Table; Data Result_Append; Length Dataset $ 32 Variable $ 9 Variable2 8; Run; Proc Sql; Delete From Result_Append; %Mend Fake_Table; %Macro Test(Var); Ods Output ResponseProfile=Res_&Var. Association=Roc_&Var. ParameterEstimates=Err_&Var.; PROC LOGISTIC DATA=Have NameLen=32 PLOTS(ONLY)=ALL; MODEL Target (Event = '1')=&Var. /SELECTION=NONE LINK=LOGIT; RUN; QUIT; Ods Output Close; Data Err2_&Var.(Keep=Variable StdErr Dataset Rename=(StdErr=Variable2)); Length Dataset $ 32; Set Err_&Var.; Where Variable="&Var."; If Variable="&Var." then Variable="StdErr_"; Dataset="&Var."; Run; Data Roc2_&Var.(Keep=Label2 nValue2 Dataset Rename=(nValue2=Variable2 Label2=Variable)); Length Dataset $ 32; Set Roc_&Var.; IF Label2="Somers' D" Or Label2="c"; If Label2="c" then Label2="Roc_"; If Label2="Somers' D" then Label2="Gini_"; Dataset="&Var."; Run; PROC SQL; Create Table Append_&Var. As Select * From Roc2_&Var. Outer Union Corr Select * From Err2_&Var.; QUIT; PROC APPEND Base=Result_Append Data=Append_&Var.; Run; %Mend Test; %Macro Test_Loop; %let i=1; %do %while(%scan(&Variable.,&i.,%str( ))~=); %Test(%scan(&Variable.,&i.,%str( ))); %let i=&i.+1; %end; %mend; %Let Variable=Variable1 Variable3 Variable4; %Fake_Table; %Test_Loop; Thank you

ertr · ‎06-28-2016

Yes, I realized late, apologize for that 😞 Thank you for trying to help me madam 🙂

ertr · ‎06-27-2016

You seem right, I will re-check it, thank you very much. I'm sorry if I tired both of you @Reeza and @Tom Thank you

ertr · ‎06-27-2016

Hello @Tom, Did you have a time to investigate my latest post. Should I accept your method as a solution. In my last sample your code gives different results that is the reason why I have some concerns whether I use your code or not. Thank you

ertr · ‎06-27-2016

Thank you very much @Tom and @Reeza, Yes, I tried the other method because if your method won't work then I will use the other method. Today I tried on real data but the results didn't match each other. I prepared new sample code. I attached the sample code because it includes many 58 dates then if I try to implement your method and try to implement manual method the results seem different. Your Code %Let StartDate=01JUN2010; %Put StartDate; %Let EndDate=31MAR2015; %Put EndDate; %Let NPeriods=%Sysfunc(Ceil(%Sysfunc(Intck(Month,"&StartDate"d,"&EndDate"D))/3)); %Put NPeriods; Data Want; Set Have; Period = &NPeriods - Int(Intck('month',Date,"&Enddate"d)/3); Run; Manual Code Data Want2; Set Have; Length Period 4.; Period = 0; If '01JUN2010'd <= Date <= '30JUN2010'd Then Period = 1; If '30JUN2010'd < Date <= '30SEP2010'd Then Period = 2; If '30SEP2010'd < Date <= '31DEC2010'd Then Period = 3; If '31DEC2010'd < Date <= '31MAR2011'd Then Period = 4; If '31MAR2011'd < Date <= '30JUN2011'd Then Period = 5; If '30JUN2011'd < Date <= '30SEP2011'd Then Period = 6; If '30SEP2011'd < Date <= '31DEC2011'd Then Period = 7; If '31DEC2011'd < Date <= '31MAR2012'd Then Period = 8; If '31MAR2012'd < Date <= '30JUN2012'd Then Period = 9; If '30JUN2012'd < Date <= '30SEP2012'd Then Period = 10; If '30SEP2012'd < Date <= '31DEC2012'd Then Period = 11; If '31DEC2012'd < Date <= '31MAR2013'd Then Period = 12; If '31MAR2013'd < Date <= '30JUN2013'd Then Period = 13; If '30JUN2013'd < Date <= '30SEP2013'd Then Period = 14; If '30SEP2013'd < Date <= '31DEC2013'd Then Period = 15; If '31DEC2013'd < Date <= '31MAR2014'd Then Period = 16; If '31MAR2014'd < Date <= '30JUN2014'd Then Period = 17; If '30JUN2014'd < Date <= '30SEP2014'd Then Period = 18; If '30SEP2014'd < Date <= '31DEC2014'd Then Period = 19; If '31DEC2014'd < Date <= '31JAN2015'd Then Period = 20; If '31JAN2015'd < Date <= '31MAR2015'd Then Period = 21; Run; Sample data set is attached. That is the reason why I try to build macro structure because there are less equal and less than symbols in the code. If your code will work, I'm gladly use. Thank you

ertr · ‎06-26-2016

Hello @Tom, I just saw your latest post, thank you very much, it is very useful for me. Now, I need to try in my real data and I will inform you. By the help of your &Nperiods macro variable I fixed my previous code, it seems proper but as I mentioned in my previous post what if Start Date is 31JUN1989 instead of 31JUL1989 so I need to replace the “1” value with “2” value in the following statement “Intnx("Month", "&StartDate"d, 1, "E");" Do you have an idea about this subject? Options Mprint; %Macro New; Data Want; Set Have; Length Period 4.; Period = 0; %Let j=1; %Do i=1 %To &NPeriods.; %If &i.=1 %Then %Do; Boundary&i. = "&StartDate"D; Boundary%Eval(&i.+1) = Intnx("Month", "&StartDate"d, &i., "E"); %End; %If &i.~=1 %Then %Do; Boundary&i. = Intnx("Month", "&StartDate"d,&j., "E"); %Let j=%Eval(&j.+3); Boundary%Eval(&i.+1) = Intnx("Month", "&StartDate"d,&j., "E"); %End; Format Boundary: Date9.; %If &i.=1 %Then %Do; If Boundary&i. <= PRO_END_DATE_YM <= Boundary%Eval(&i.+1) Then Period =&i.; %End; %Else %Do; If Boundary&i. < PRO_END_DATE_YM <= Boundary%Eval(&i.+1) Then Period =&i.; %End; %End; Run; %Mend New; %New; And your code goes like this Data Want ; Set Have ; Period = &nperiods - Int(Intck('Month',Pro_End_Date_Ym,"&enddate"d)/3); Expected = 0; If '31JUL1989'd < Pro_End_Date_Ym <= '31AUG1989'd Then expected = 1; If '31AUG1989'd < Pro_End_Date_Ym <= '30NOV1989'd Then expected = 2; If '30NOV1989'd < Pro_End_Date_Ym <= '28FEB1990'd Then expected = 3; Run; Thank you

ertr · ‎06-26-2016

Hello @Rezza, I tried to implement your foregoing articles and I think I still need to decrease some steps to build desired dynamic structures. 1st -> I need to give parametric value instead of 3 in the following statement “%Do i=1 %To 3;” but question mark in my mind is how can I determine this value being a parametric.how can I know this value? 2nd-> I need to enhance the following statement values(1,4,7) being a parametric “Intnx("Month", "&StartDate"d, 1, "E");, Intnx("Month", "&StartDate"d, 4, "E");, Intnx("Month", "&StartDate"d, 7, "E"); Then it will probably seem proper, what do you think @Reeza? %Let StartDate=31JUL1989; %Let EndDate=28FEB1990; Data Have; Format Pro_End_Date_Ym DATE9.; Infile Datalines Missover; Input Pro_End_Date_Ym; Datalines; 10826 10827 10828 10829 10830 10850 10870 10880 10991 10999 ; Run; Options Mprint; %Macro New; Data Want; Set Have; Length Period 4.; Period = 0; %Do i=1 %To 3; %If &i.=1 %Then %Do; Boundary&i. = "&StartDate"D; Boundary%Eval(&i.+1) = Intnx("Month", "&StartDate"d, 1, "E"); %End; %If &i.=2 %Then %Do; Boundary&i. = Intnx("Month", "&StartDate"d, 1, "E"); Boundary%Eval(&i.+1) = Intnx("Month", "&StartDate"d, 4, "E"); %End; %If &i.=3 %Then %Do; Boundary&i. = Intnx("Month", "&StartDate"d, 4, "E"); Boundary%Eval(&i.+1) = Intnx("Month", "&StartDate"d, 7, "E"); %End; Format Boundary: Date9.; %If &i.=1 %Then %Do; If Boundary&i. <= PRO_END_DATE_YM <= Boundary%Eval(&i.+1) Then Period =&i.; %End; %Else %Do; If Boundary&i. < PRO_END_DATE_YM <= Boundary%Eval(&i.+1) Then Period =&i.; %End; %End; Run; %Mend New; %New; But I just realized one thing, you used “1” value in your-> “Intnx("Month", "&StartDate"d, 1, "E"); “ statement because there is just one month between 31JUL1989 and 31AUG1989. What if Start Date is given being a 31JUN1989 then it should be Intnx("Month", "&StartDate"d, 2, "E"); and continue like -> Intnx("Month", "&StartDate"d, 5, "E");, Intnx("Month", "&StartDate"d, 8, "E"); So how can I provide this kind of dynamic structure over your code?

ertr · ‎06-26-2016

Hello again @Reeza, Let me try to tell you, I think I cannot get your explanations 😞 Sorry We define the date values. First one is Start Date and second one is End Date. We need to use as a base for End Date. It should decrease three(month) by three. But if End Date will excess Start Date then it should be over on Start Date and symbol should like -> "<=". Actually, your following code is correct but the point is that I should make suitable this code for every input data set. If there are 10 Boundary in your following code so there will be 10 periods For example, in you boundary2, boundary3 and boundary4 statements 1,4 and 7 values should be parametric. Periods also should be parametric and boundaries too. I try yo build this structure. I hope I could make myself clear. %Let StartDate=31JUL1989; %Let EndDate=28FEB1990; Data Want; sest have; Length Period 4.; Period = 0; boundary1 = "&startDate"d; boundary2 = intnx("month", "&startDate"d, 1, 'e'); boundary3 = intnx("month", "&startDate"d, 4, 'e'); boundary4 = intnx("month", "&startDate"d, 7, 'e'); format boundary: date9.; if boundary1 <= PRO_END_DATE_YM <= boundary2 then Period = 1;/*Decreasing 1 month because it cannot excess the StartDate*/ if boundary2 < PRO_END_DATE_YM <= boundary3 then Period = 2;/*Decreasing 3 months*/ if boundary3 < PRO_END_DATE_YM <= boundary4 then Period = 3;/*Decreasing 3 months*/ Thank you,

ertr · ‎06-26-2016

Hello @Reeza and @Tom, I’m so sorry I tried something but I could not handle it. Actually, I understood @Tom’s latest method and it worked in my sample data set fine but it didn’t work in my real data set. The results didn’t match the manaul method’s results 😞 Do you have an idea why the results cannot match with each other? I also try to create macro structure but I couldn’t handle it @Reeza I’m so sorry. My following sample does not seem fine, I know, I’ll re-examine it but could you help me, please? On the other hand, first row should be like that -> if '31JUL1989'd <=(Less Than or Equal To) PRO_END_DATE_YM <= '31AUG1989'd then Period = 1; Thank you very much both of you again. Data Want; Set Have; Length Period 4.; Period = 0; if '31JUL1989'd <= PRO_END_DATE_YM <= '31AUG1989'd then Period = 1; /*Decreasing 1 monthbecause it cannot excess the StartDate*/ /*This part changed being Less Than or Equal To instead of Less Than*/ .......; Run; Data Have; Format Pro_End_Date_Ym DATE9.; Infile Datalines Missover; Input Pro_End_Date_Ym; Datalines; 10826 10827 10828 10829 10830 10850 10870 10880 10991 10999 11111 11222 11333 11444 11555 11666 ; Run; %Let Start_Date=31JUL1989; %Let End_Date=31DEC1991; %Macro Date_Intervals(A,B,C,DataIn,DataOut,Date_Start,Date_End); Data DataOut.; Set DataIn.; Length Period 4.; Period = 0; %If &A.=1 %Then %Do; Boundary&A. = "&Date_Start"d; %End; Boundary&A.=Intnx("Month", "&Date_Start"d, &B., "E"); Format Boundary: Date9.; If Boundary&A. < PRO_END_DATE_YM <= boundary&C. then Period = &i.; Run; %Mend Date_Intervals; %Macro Dynamic; %Let i=1; %Let j=1; %Let x=2; %Do %Until(&End_Date.); %Date_Intervals(&i.,&j.,&x.,Have,Want,&Start_Date.,&End_Date.); %Let i=&i.+1; %Let j=&j.+3; %Let x=&x.+1; %End; %Mend; %Dynamic;

Online Status	Offline
Date Last Visited	‎05-31-2023 09:26 AM

Re: When expanding data for modeling, there is an issue with overlappi...

Re: When expanding data for modeling, there is an issue with overlappi...

When expanding data for modeling, there is an issue with overlapping d...

What is the equivalent of BINOM.DIST.RANGE in SAS?

Re: About LGD Modeling Approach

About LGD Modeling Approach

Re: How to Get one row before depending on a condition?

How to Get one row before depending on a condition?

Anomaly Detection - Unsupervised Learning without VIYA & Alternative A...

Re: How to define optimal Date Range for Model Development Mass?

Re: How to Standardize Text Values with Text Miner in SAS Enterprise M...

Re: Best Practice of Ministry of Customs and Commerce Fraud - From Beg...

Re: SAS Fraud Management

Re: How to - Round the Date Variable to Latest Day of Month?

Re: Preparing Two Data Sets Which Trigger Each Other

Best Practice of Ministry of Customs and Commerce Fraud - From Beginni...

Re: Best Practice of Ministry of Customs and Commerce Fraud - From Beg...

Preparing Two Data Sets Which Trigger Each Other

Re: Dynamic Macro Loop - Hold One Data Set and Compare with Other Data...

Re: Dynamic Macro Loop - Hold One Data Set and Compare with Other Data...

Re: Dynamic Macro Loop - Hold One Data Set and Compare with Other Data...

Re: Dynamic Macro Loop - Hold One Data Set and Compare with Other Data...

How to - Get Specified Values By Taking Specified Digits From Variable...

Macro Loop Can't Resolve First Variable

Re: Dynamic Macro Loop - To Create An Automatic Loop

Re: Dynamic Macro Loop - To Create An Automatic Loop

Re: Dynamic Macro Loop - To Create An Automatic Loop

Re: Dynamic Macro Loop - To Create An Automatic Loop

Re: Dynamic Macro Loop - To Create An Automatic Loop

Re: Dynamic Macro Loop - To Create An Automatic Loop

Re: Dynamic Macro Loop - To Create An Automatic Loop

Re: Dynamic Macro Loop - To Create An Automatic Loop