Mark variable as 1 ,2 ,3 etc

MadMax — Fri, 01 May 2020 09:21:58 GMT

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 ?

Re: Mark variable as 1 ,2 ,3 etc

ballardw — Fri, 01 May 2020 19:51:36 GMT

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.

topic Mark variable as 1 ,2 ,3 etc in SAS Programming

Mark variable as 1 ,2 ,3 etc

Re: Mark variable as 1 ,2 ,3 etc