Please see the data step below in the preferred form of providing data. A data step that runs allows us to test code against the data. Paste code into a code box opened with the forum's </> or "running man" icon to preserve formatting. The message windows on this format will reformat code or log entries.
data have;
input Account $ Group :$20. Amount ;
datalines;
A1 Collected_202003 100.00
A1 Vat_14_202003 0.00
A1 Vat_15_202003 15.00
A1 Collected_202004 150.00
A1 Vat_14_202004 0.00
A1 Vat_15_202004 18.00
B1 Collected_202003 100.00
B1 Vat_14_202003 0.00
B1 Vat_15_202003 15.00
;
data want;
set have;
by account;
retain grpmon;
if first.account then do;
grpmon= input(substr(group,length(group)-5),yymmn6.);
end;
thismon =input(substr(group,length(group)-5),yymmn6.);
need = intck('month',grpmon,thismon) +1;
drop grpmon thismon;
run;
This assumes that the data is sorted as needed by the account. If the accounts are not actually in sort order but grouped correctly add NOTSORTED to the BY statement.
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.
... View more
