Thankyou. Crystal clear ... View more

Thankyou very much for your help. Previously I figured out on seperating the number with hiphen using substr but, I was unable to convert the 15 digit number value to character value. I've a minor concern here, can't we achieve the required output without changing the length of the variable, please let me know. Anyhow, please fill your heart heartily thanked. ... View more

If your question is regarding, how did they store a 15 digits number into a sas table by avoiding E notation. They might have used best16. Format to avoid E Notation. ... View more

Cards; 1414 67 114 RIG 08MAR2022 01FEB2022 1 1414 67 114 GIR 01FEB2022 30JAN2022 2 ; Is that possible not  to pick the 1st appearance of Id,roll, num but product name changes and DATE1 >= DATE2  meets but when a new product comes for same id, roll, num and if the amount for new product is > the amount of old product for s Required output 1414 67 114 RIG 01FEB2022 30JAN2022 1 ame id, roll, num  that new product row shouldn't be picked. Could you please add this new condition to your code. ... View more

Thanks for providing the code. You used want and increase for the sake of testing but when comes my data I can't use them bcuz the data which I provided was already in a table, from that I picked some data and posted here. Could you please ask your question again, I didn't get it and I didn't say it should select other record. ... View more

cards; 1411 69 115 ABC 01JAN2022 20APR2022 1 1411 69 115 ABC 01FEB2022 30JAN2022 2 1411 69 115 DEF 01FEB2022 30JAN2022 3 1411 69 115 DEF 09MAR2022 11APR2022 1 1411 69 117 DEF 24FEB2022 24FEB2022 2 1412 70 116 JKL 03JUN2022 21FEB2022 1 1412 70 116 JKL 04MAR2022 29JAN2022 2 1412 70 117 XYZ 01MAR2022 06MAR2022 3 1412 70 117 XYZ 03MAY2022 01JAN2022 4 1413 91 213 WOP 05JAN2022 03MAR2022 1 1413 91 213 WOP 05MAY2022 03MAR2022 3 1413 91 213 WXY 07MAY2022 09FEB2022 2 ; here's the sample data which I added some records to be suitable for adding first.product alongside first.num. Req output : 1411 69 115 ABC 01FEB2022 30JAN2022 2 1411 69 115 DEF 01FEB2022 30JAN2022 3 1411 69 117 DEF 24FEB2022 24FEB2022 2 1412 70 116 JKL 03JUN2022 21FEB2022 1 1412 70 117 XYZ 03MAY2022 01JAN2022 4 1413 91 213 WOP 05MAY2022 03MAR2022 3 1413 91 213 WXY 07MAY2022 09FEB2022 2 ; After picking 1st appearance of Id, roll, num and product. Thankyou.       ... View more

Hii Tom, I've a small change in your code. Could you please add product as well as first.product in your code. I sincerely apologise for coming back to you again. In the 1st rule, the data should be picked on 1st appearance of Id,roll,num as well 1st appearance of product too. Could you please make this small change. Thankyou. ... View more

This 2nd case is working much better than TOM's. Thankyou very much both of you. Now I got the accurate output which I require. Your generosity overwhelmed me. ... View more

FYI, the data which I posted here was just a small piece of massive data. Approximately 90K records. May I know why did you use first.num only, at the back of my mind I thought concatenating id,roll, num as a single value and using that value in first. may works well. Doesn't that concatenated value helpful?. I request you to Please let me know. Thank you very much for providing the code ... View more

It because of that's how my data is, previously the data which I sent is a sorted one, which I did this to be everything in sorted order to make the job easier for both the two rules. But I didn't get the required output which I posted that's why I just wanted to post the data as is. ... View more

data have; input date1 :date. date2 :date. id:4. roll:2. num: 3. amount:2. Product :$5.; format date1 date2 date9.; cards; 01FEB2022 30JAN2022 1411 69 115 2 DEF 03MAR2022 21FEB2022 1412 70 116 1 JKL 01FEB2022 30JAN2022 1411 69 115 3 GHI 01JAN2022 20APR2022 1411 69 115 1 ABC 04MAR2022 29JAN2022 1412 70 116 2 JKL 06MAR2022 06MAR2022 1412 70 117 3 XYZ 05JAN2022 03MAR2022 1413 91 213 1 WOP 07MAY2022 09FEB2022 1413 91 213 2 WXY ; Required Output: 01FEB2022 30JAN2022 1411 69 115 2 DEF 03MAR2022 21FEB2022 1412 70 116 1 JKL 06MAR2022 06MAR2022 1412 70 117 3 XYZ 07MAY2022 09FEB2022 1413 91 213 2 WXY Firstly this data is an unsorted one so, it must be sorted by id,roll,num, product,DATE1 variables prior to the two cases. 1st rule: The 1st appearance of Id, roll, num should be picked only after it meets the condition DATE1 >= DATE2. In this id, roll, num are primary keys and DATE1, DATE2, AMOUNT, PRODUCT has relation with Id,roll, num. Explanation for 1st rule: In the above given data, If the 1st appearance of Id, roll, num doesn't meet the condition DATE1>= DATE2.   This is where 2nd rules comes in : If the 1st appearance of Id, roll, num doesn't meet the condition DATE1 >= DATE2, then when there's a increase in amount value compared to previous value then the first increase of amount row should be picked and that should also match DATE1 >= DATE2..   2nd rule explanation: for the 1st id, roll, num it should pick the first increase in amount. ... View more

Hii Thanks for responding. I'm unable to find that option, that's why I was typing it as text. Would you mind walking me through that ... View more

I've already done this by adding DATE1 in proc sort but when I didn't use that in the BY statement of your code. It got an error saying BY variables are not sorted properly. ... View more

Please do read this, if this doesn't help, you can leave as-is without answering. Thankyou. Like I said, I sincerely apologise for changing my mind but, after doing some testing I came up with another thought which suits the blueprint of the output dataset. Please do pick the rows which has first increase of amount. I'm again re-typing the things which I require from this below data. 01JAN2022 20APR2022 1411 69 115 1 01FEB2022 30JAN2022 1411 69 115 2 01FEB2022 30JAN2022 1411 69 115 3 03MAR2022 21FEB2022 1412 70 116 1 04MAR2022 29JAN2022 1412 70 116 2 1st rule: The 1st appearance of Id, roll, num should be picked only after it meets the condition DATE1 >= DATE2. In this id, roll, num are primary keys and DATE1, DATE2, AMOUNT, PRODUCT has relation with Id,roll, num. Explanation for 1st rule: In the above given data, the 1st appearance of Id, roll, num doesn't meet the condition DATE1>= DATE2. This is where 2nd rules comes in : If the 1st appearance of Id, roll, num (1411 69 115)doesn't meet the condition DATE1 >= DATE2, then when there's a increase in amount value compared to previous value then the first increase of amount row should be picked and that should also match DATE1 >= DATE2. Continuing 1st rule : For the next id, roll, num i.e 1412 70 116 this should be picked cuz this is the 1st appearance of Id, roll, num and it meets the condition DATE1>= DATE2. But when I use your code it didn't pick the record which has date 03MAR2022 instead it picked both 03MAR2022 and 04MAR2022. 2nd rule explanation: for the 1st id, roll, num (1411 69 115) for this it should pick the first increase in amount. I.e 01FEB2022 30JAN2022 1411 69 115 2 instead of 01FEB2022 30JAN2022 1411 69 115 3. I hope this makes more sense. ... View more