About MV04

MV04 · ‎05-08-2019

Thanks for your reply Kurt How do I do that

MV04 · ‎05-08-2019

Hi All, I am looking for a procedure or some way to update a DB2 table in a DB2 library from another DB2 table in a SAS librray. The DB2 table which is in DB2 library which needs updating is empty.

MV04 · ‎04-15-2019

Thanks so much Yabwon . That did work.

MV04 · ‎04-15-2019

HI Yabwon , That's awsome but I am missing the last record so for exampl if I have got only these three 30dec2008,1221,20 01feb2009,1221,30 01mar2009,1222,10 then I am not geting 01mar2009,1222,10 in my result

MV04 · ‎04-15-2019

Hi Draycut, This is what I am saying that loop is stopping on first BY varibale and not going any futher. We needs to get the other dates as below for other BY group as well. so for 01mar2009 it should show 1222 10 that row is missng

MV04 · ‎04-15-2019

Hi Yabwon I should get amt as 20 for the below 30DEC2008 1221 30 31DEC2008 1221 30 01JAN2009 1221 30 02JAN2009 1221 30 03JAN2009 1221 30 04JAN2009 1221 30 05JAN2009 1221 30 06JAN2009 1221 30 07JAN2009 1221 30 08JAN2009 1221 30 09JAN2009 1221 30 10JAN2009 1221 30 11JAN2009 1221 30 as you can see from the program below from 30dec2008 until 31jan2009 amt is 20

MV04 · ‎04-15-2019

Apologies Draycut. The above reply ment for you.

MV04 · ‎04-15-2019

Thanks for the reply Ballardw. but it seemlike it is not switching on to the next BY group so the result is stopping at the 1st BY grup (1221) 1221 30 18FEB2009 1221 30 19FEB2009 1221 30 20FEB2009 1221 30 21FEB2009 1221 30 22FEB2009 1221 30 23FEB2009 1221 30 24FEB2009 1221 30 25FEB2009 1221 30 26FEB2009 1221 30 27FEB2009 1221 30 28FEB2009 Nothing after the above

MV04 · ‎04-15-2019

I have got a dataset as below and I need to loop through the dates increaed by 1 in each by group of ID data have; format eff_dat date9. ; infile cards dlm = ',' ; input eff_dat date9. id amt; cards; 30dec2008,1221,20 01feb2009,1221,30 01mar2009,1222,10 01apr2009,1222,15 ;run; I need the result as below 30-Dec-08 1221 20 31-Dec-08 1221 20 01-Jan-09 1221 20 02-Jan-09 1221 20 03-Jan-09 1221 20 04-Jan-09 1221 20 05-Jan-09 1221 20 06-Jan-09 1221 20 07-Jan-09 1221 20 08-Jan-09 1221 20 09-Jan-09 1221 20 10-Jan-09 1221 20 11-Jan-09 1221 20 12-Jan-09 1221 20 13-Jan-09 1221 20 14-Jan-09 1221 20 15-Jan-09 1221 20 16-Jan-09 1221 20 17-Jan-09 1221 20 18-Jan-09 1221 20 19-Jan-09 1221 20 20-Jan-09 1221 20 21-Jan-09 1221 20 22-Jan-09 1221 20 23-Jan-09 1221 20 24-Jan-09 1221 20 25-Jan-09 1221 20 26-Jan-09 1221 20 27-Jan-09 1221 20 28-Jan-09 1221 20 29-Jan-09 1221 20 30-Jan-09 1221 20 31-Jan-09 1221 20 01-Feb-09 1221 30 02-Feb-09 1221 30 03-Feb-09 1221 30 04-Feb-09 1221 30 05-Feb-09 1221 30 06-Feb-09 1221 30 07-Feb-09 1221 30 08-Feb-09 1221 30 09-Feb-09 1221 30 10-Feb-09 1221 30 11-Feb-09 1221 30 12-Feb-09 1221 30 13-Feb-09 1221 30 14-Feb-09 1221 30 15-Feb-09 1221 30 16-Feb-09 1221 30 17-Feb-09 1221 30 18-Feb-09 1221 30 19-Feb-09 1221 30 20-Feb-09 1221 30 21-Feb-09 1221 30 22-Feb-09 1221 30 23-Feb-09 1221 30 24-Feb-09 1221 30 25-Feb-09 1221 30 26-Feb-09 1221 30 27-Feb-09 1221 30 28-Feb-09 1221 30 01-Mar-09 1222 10 02-Mar-09 1222 10 03-Mar-09 1222 10 04-Mar-09 1222 10 05-Mar-09 1222 10 06-Mar-09 1222 10 07-Mar-09 1222 10 08-Mar-09 1222 10 09-Mar-09 1222 10 10-Mar-09 1222 10 11-Mar-09 1222 10 12-Mar-09 1222 10 13-Mar-09 1222 10 14-Mar-09 1222 10 15-Mar-09 1222 10 16-Mar-09 1222 10 17-Mar-09 1222 10 18-Mar-09 1222 10 19-Mar-09 1222 10 20-Mar-09 1222 10 21-Mar-09 1222 10 22-Mar-09 1222 10 23-Mar-09 1222 10 24-Mar-09 1222 10 25-Mar-09 1222 10 26-Mar-09 1222 10 27-Mar-09 1222 10 28-Mar-09 1222 10 29-Mar-09 1222 10 30-Mar-09 1222 10 31-Mar-09 1222 10

MV04 · ‎04-14-2019

HI Andreas , I urgently need help on this query. Did you manage to run the code and create the results please .

MV04 · ‎04-14-2019

data table1; format Effective_date date9.; infile cards dlm= "," dsd; input id rate Effective_date ddmmyy10.; cards ; 123,5.62,18/05/2009 123,4.88,01/06/2009 123,4.25,01/09/2009 123,4.12,01/12/2009 123,4.25,01/03/2010 123,4.37,01/03/2011 123,4.5 ,01/09/2011 123,4.62,01/12/2011 123,4.5 ,01/06/2012 123,4.25,03/09/2012 123,4.12,03/12/2012 ;run; data table2; format Formmated_Date ddmmyy10. id 3. Close_dt ddmmyy10. Open_dt ddmmyy10.; infile cards dlm= "," dsd ; input Formmated_Date ddmmyy10. ID 3. Close_dt ddmmyy10. Open_dt ddmmyy10.; cards ; 18/05/2009,123,30/01/2015,18/05/2009 19/05/2009,123,30/01/2015,18/05/2009 20/05/2009,123,30/01/2015,18/05/2009 21/05/2009,123,30/01/2015,18/05/2009 22/05/2009,123,30/01/2015,18/05/2009 23/05/2009,123,30/01/2015,18/05/2009 24/05/2009,123,30/01/2015,18/05/2009 25/05/2009,123,30/01/2015,18/05/2009 26/05/2009,123,30/01/2015,18/05/2009 27/05/2009,123,30/01/2015,18/05/2009 28/05/2009,123,30/01/2015,18/05/2009 29/05/2009,123,30/01/2015,18/05/2009 30/05/2009,123,30/01/2015,18/05/2009 31/05/2009,123,30/01/2015,18/05/2009 01/06/2009,123,30/01/2015,18/05/2009 02/06/2009,123,30/01/2015,18/05/2009 03/06/2009,123,30/01/2015,18/05/2009 04/06/2009,123,30/01/2015,18/05/2009 05/06/2009,123,30/01/2015,18/05/2009 06/06/2009,123,30/01/2015,18/05/2009 07/06/2009,123,30/01/2015,18/05/2009 08/06/2009,123,30/01/2015,18/05/2009 09/06/2009,123,30/01/2015,18/05/2009 10/06/2009,123,30/01/2015,18/05/2009 11/06/2009,123,30/01/2015,18/05/2009 12/06/2009,123,30/01/2015,18/05/2009 13/06/2009,123,30/01/2015,18/05/2009 14/06/2009,123,30/01/2015,18/05/2009 15/06/2009,123,30/01/2015,18/05/2009 16/06/2009,123,30/01/2015,18/05/2009 17/06/2009,123,30/01/2015,18/05/2009 18/06/2009,123,30/01/2015,18/05/2009 19/06/2009,123,30/01/2015,18/05/2009 20/06/2009,123,30/01/2015,18/05/2009 21/06/2009,123,30/01/2015,18/05/2009 22/06/2009,123,30/01/2015,18/05/2009 ;run;

MV04 · ‎04-14-2019

I need to merge two datasets on ID but I want rate value to be same from the top date of table2 until date chnages in table1 so for exampe in table 1 effective date is 18may09 and in table two it is from 18may09 untill th expiry date so I need t get the rate until 18maychanges to 1st June in Table I have got two dtastes as below Table1 ID rate Effective date 123 5.62 18-May-09 123 4.88 01-Jun-09 123 4.25 01-Sep-09 123 4.12 01-Dec-09 123 4.25 01-Mar-10 123 4.37 01-Mar-11 123 4.5 01-Sep-11 123 4.62 01-Dec-11 123 4.5 01-Jun-12 123 4.25 03-Sep-12 123 4.12 03-Dec-12 Table 2 Formmated Date ID close date open dare 18-May-09 30-Apr-10 123 30-Jan-15 18-May-09 19-May-09 30-Apr-10 123 30-Jan-15 18-May-09 20-May-09 30-Apr-10 123 30-Jan-15 18-May-09 21-May-09 30-Apr-10 123 30-Jan-15 18-May-09 22-May-09 30-Apr-10 123 30-Jan-15 18-May-09 23-May-09 30-Apr-10 123 30-Jan-15 18-May-09 24-May-09 30-Apr-10 123 30-Jan-15 18-May-09 25-May-09 30-Apr-10 123 30-Jan-15 18-May-09 26-May-09 30-Apr-10 123 30-Jan-15 18-May-09 27-May-09 30-Apr-10 123 30-Jan-15 18-May-09 28-May-09 30-Apr-10 123 30-Jan-15 18-May-09 29-May-09 30-Apr-10 123 30-Jan-15 18-May-09 30-May-09 30-Apr-10 123 30-Jan-15 18-May-09 31-May-09 30-Apr-10 123 30-Jan-15 18-May-09 01-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 02-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 03-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 04-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 05-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 06-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 07-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 08-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 09-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 10-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 11-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 12-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 13-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 14-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 15-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 16-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 17-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 18-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 19-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 20-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 21-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 22-Jun-09 30-Apr-10 123 30-Jan-15 18-May-09 and I want result like this Result ID Rate Formatted date 123 5.62 18-May-09 123 5.62 19-May-09 123 5.62 20-May-09 123 5.62 21-May-09 123 5.62 22-May-09 123 5.62 23-May-09 123 5.62 24-May-09 123 5.62 25-May-09 123 5.62 26-May-09 123 5.62 27-May-09 123 5.62 28-May-09 123 5.62 29-May-09 123 5.62 30-May-09 123 5.62 31-May-09 123 5.62 01-Jun-09 123 4.88 02-Jun-09 123 4.88 03-Jun-09 123 4.88 04-Jun-09 123 4.88 05-Jun-09 123 4.88 06-Jun-09 123 4.88 07-Jun-09 123 4.88 08-Jun-09 123 4.88 09-Jun-09 123 4.88 10-Jun-09 123 4.88 11-Jun-09 123 4.88 12-Jun-09 123 4.88 13-Jun-09 123 4.88 14-Jun-09 123 4.88 15-Jun-09 123 4.88 16-Jun-09 123 4.88 17-Jun-09 123 4.88 18-Jun-09 123 4.88 19-Jun-09 123 4.88 20-Jun-09 123 4.88 21-Jun-09 123 4.88 22-Jun-09

MV04 · ‎04-09-2019

The output dataset should be something like below id1 open_date amt_d1 amt_d2 123567 01-Apr-09 5 6 123567 10-Apr-09 4 7 123567 01-May-09 2 4 123567 01-Jun-10 5 1 123567 01-Jul-10 1 5 123567 01-Sep-10 2 3 123567 01-Oct-10 6 8 123567 03-Dec-12 4 2 123568 02-Sep-11 6 5 123568 02-Apr-09 5 3 123568 12-Apr-09 4 5 123568 11-May-09 2 7 so amt is the last amt prior to each date as you loop through the dates in between d1 and d2 so if we loop through open date in d1 then the dtes will be like 01-apr,02-apr,03-apr,04-apr,05-apr,06-apr and so on and these dates we need to compare with the open date from d2 so if the date say 01apr2009 from d1 falls between the dates 02ma2009 till 11apr2009 then return the amt at 02mar2009,if its not falling between those dates then check the next two dates (make sure the open dte in d2 is sorted in ascending order)

MV04 · ‎04-09-2019

Hi, I have got two datasets as D1 and D2 D1 id2 id1 open_date amt 122234 123567 01-Apr-09 5 122234 123567 10-Apr-09 4 122234 123567 01-May-09 2 122234 123567 01-Jun-10 5 122234 123567 01-Jul-10 1 122234 123567 01-Sep-10 2 122234 123567 01-Oct-10 6 122234 123567 03-Dec-12 4 134221 123568 02-Sep-11 6 134222 123568 02-Apr-09 5 134222 123568 12-Apr-09 4 134222 123568 11-May-09 2 134222 123568 12-Jun-10 5 134222 123568 02-Jul-10 1 D2 id2 open_date amt 122234 02-Mar-09 6 122234 11-Apr-09 7 122234 02-May-09 4 122234 05-Jun-09 1 122234 02-Jul-10 5 122234 05-Sep-10 3 122234 04-Nov-10 8 134221 11-Sep-11 2 134222 10-May-09 5 134222 25-May-09 3 134222 26-May-09 5 134222 28-May-09 7 Now the task is : I need to take all those amounts from D2 where any date per account from d1 > (all dates from d2) The two datasets can be linked on D2. all I am trying to create a macro which will compare the d1 and d2 on acc numbers and check the dates from d1 into d2 and whereever date from d1 is greater than d2 get the amount of the prior date

MV04 · ‎04-09-2019

Thanks so much Reeza It worked

Online Status	Offline
Date Last Visited	‎01-20-2020 08:32 AM

Re: how do I count difference in dates and a counter on that

Re: how do I count difference in dates and a counter on that

Re: how do I count difference in dates and a counter on that

how do I count difference in dates and a counter on that

Re: joining on same variable date n key but different values

Re: joining on same variable date n key but different values

Re: joining on same variable date n key but different values

Re: joining on same variable date n key but different values

Re: joining on same variable date n key but different values

joining on same variable date n key but different values

Re: Transpose data in 5 variables

Re: Updating DB2 table from another DB2 table

Updating DB2 table from another DB2 table

Re: how to loop through dates within a by group

Re: how to loop through dates within a by group

Re: how to loop through dates within a by group

Re: how to loop through dates within a by group

Re: how to loop through dates within a by group

Re: how to loop through dates within a by group

how to loop through dates within a by group

Re: merge on ID

Re: merge on ID

merge on ID

Re: how to create a function in a data step using a dataset

how to create a function in a data step using a dataset

Re: Value increases for an ID until a milit