Thanks for the extra versions Tom! Cool that I don't need to concatenate this way! Will use that then. 🙂 ... View more

Hi Tom, I did try the Inputn function but it didn't work either, so I gave up. Yes, I do need the macro to look like a string initially because it's being used elsewhere as well in that format. So I wanted to kill two birds with one stone essentially. This is exactly what I needed. Thanks for your response as well. ... View more

Hi Astounding, it worked! Thank you so much! So I had to use the %sysfunc again (like Shmuel said) but remove the "cats". I wouldn't have guessed in a million years. I did try the inputn but always kept the "cats" in.Thank you so much for your help! ... View more

Hi Shmuel, this gives me the same error message. ... View more

Hi Smuel,   This is the error message I get when I run this line of code:   %let MonthEnd = %sysfunc(intnx(month,input(cats(&filedate.,01),yymmdd8.),0,E),date9.);   ERROR: Required operator not found in expression: input(cats(201906,01),yymmdd8.) ERROR: Argument 2 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number. ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.   I don't understand what it means and I've tried many different variations. ... View more

You are absolutely right @novinosrin ! Neither of the two conditions you mentioned is true for my real data. It's not sorted by TMP and TMP can be taking any integer value, sometimes the same one, for many observations. Thank you very much for your help! 🙂 ... View more

Apologies @novinosrin when I re-ran it for the amended example, your code worked:   Account TMP col_want novinosrin 12345 1 1 1 12345 2 1 1 12345 3 1 1 12345 4 1 1 12345 1 2 2 12345 2 2 2 12345 1 3 3 12367 1 1 1 23456 1 1 1 23456 2 1 1 56789 1 1 1 56789 2 1 1 56789 3 1 1 56789 4 1 1 56789 5 1 1 56789 1 2 2   However, for some reason it doesn't work for my real data. But it worked for the sample, so thank you very much! ... View more

Hi @novinosrin, thanks for your code. Although it works for the example I provided initially, it doesn't seem to work for my real data nor for the amended data example I posted above. But it's very good and simple. Thank you! ... View more

Thank you so much PaigeMiller! This is amazing! I spent so many hours on this 😛 I had to modify your code slightly as it didn't work with more accounts. For example, if the data I had provided was:   data file1; input Account TMP; datalines; 12345 1 12345 2 12345 3 12345 4 12345 1 12345 2 12345 1 56789 1 56789 2 56789 3 56789 4 56789 5 56789 1 12367 1 23456 1 23456 2 ; run; proc sort data=file1; by account;run;   your code would give 2 as the first value for the last group. So I amended it to do this:   data want; set file1; by account; prev_tmp=lag(tmp); if account~=lag(account) then prev_tmp=tmp; if first.account then col_want=1; if prev_tmp>tmp then col_want+1; drop prev_tmp; run;   You've helped me so much! Thanks again! ... View more