Mark variable as 1 ,2 ,3 etc

[MadMax]

Hi, 

I have a dataset like below  

Account                   Group                                            Amount                       NEED

A1                            Collected_202003                        100.00                         1

A1                            Vat_14_202003                             0.00                            1

A1                            Vat_15_202003                           15.00                            1

A1                            Collected_202004                         150.00                         2

A1                            Vat_14_202004                             0.00                             2

A1                            Vat_15_202004                           18.00                            2

B1                            Collected_202003                        100.00                          1

B1                            Vat_14_202003                             0.00                            1

B1                            Vat_15_202003                           15.00                            1

B1                            Collected_202004                         150.00                         2

B1                            Vat_14_202004                             0.00                            2

B1                            Vat_15_202004                           18.00                            2

I need the NEED column. 

What this will do is it will basically tell me that anything that ends with the first YYYYMM  (which is 202003 in this case) will be marked as 1 , the following month will be marked as 2 etc.  (The NEED columns is looking at what run month we are in , 1st, 2nd , 3rd etc.) .  I hope that makes sense ?  

Please will you try and assist me ? 

[ballardw · Accepted solution]

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-dat... 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.