Assign New number by checking Previous Number.

[SASuserlot]

Hi, I  have the following situation where I need to check the last number  in a group based on that I need to assign next number.

In my example. I expected each ID have  Vnumber upto  6 ( svisit= ended) continuously.  If  its not the case ( in my case IDs 001, 002, 003) numbers are Ended after vsnmum 3, 2, 1.01 respectively. So I need to check each ID if they have the  vnumber  =6 ( In my case each ID will have it) and check the previous number  and assign  next number to newvaraiarble 'Nvisitn' ( I can format nvisits later).  ID-001 ended after vnumber =3 (001, 3 , Visit 3 , 2022-10-12,) So I will expect Ended Visit for this ID should have Nvisitn =4. 

Note: ID=004 have all vnumbers so , I don't expect any changes in nvisitn. ID 003 have 1.01 which  will be considered as 1

How I want. ( please ignore the svisit as full name (Visit 1). Its fine to have the terminology as in DATA 'VS1'  or 'End'. Unfortunately I deleted the table before correcting it)

data visit;
input ID $1-3 Vnumber $5-9 Svisit $10-13 date ;
informat date ddmmyy10.;
format date date9.;
cards;
001	1	 Vs1	10/10/2022
001	2	 Vs2	11/10/2022
001	3	 Vs3	12/10/2022
001	6	 End	13/10/2022
002	1	 Vs1	10/10/2022
002	2	 Vs2	11/10/2022
002	6	 End	12/10/2022
003	1	 Vs1	10/10/2022
003	1.01 uns	11/10/2022
003	6	 End	12/10/2022
004	1	 Vs1	10/10/2022
004	2	 Vs2	11/10/2022
004	3	 Vs3	13/10/2022
004	4	 Vs4	16/10/2022
004	5	 Vs5	17/10/2022
004	6	 End	18/10/2022
;
run;

[ballardw · Accepted solution]

The real challenge involves any rules around your decimal values for vnumber. If you have any values for Vnumber with a decimal portion other than 1.01 you need to provide them. And the rules for how to use them. What if you have something with 4.01, 4.02 and 4.03 for example? This is a case of one example may not provide a rule that can be coded properly for other values.

Do you have other "visit" values beside "Vs1" etc.  "uns" and "End"?

A side note, it is not good practice to show a picture with one set of values, i.e. "Visit 1" or  "Ended" and then provide example data with different values "Vs1" or "End" as solutions provided may not work with your actual data.

[SASuserlot]

Answer to your first question.

1. It doesn't not matter whatever the number after the decimal is, we only consider the number before the integer value. 1.09= 1,         2.05= 2 etc.

2.  Visits I can format based the new nvisit. Basically  visit is created by concatenating the 'VS' and Visitnum. If visitnum=6  then visit='End' , the decimals indicate the uns ( unscheduled visit). for sorting purpose we can use visitnum and date.  We don't have any more visits other than the above mentioned.

3. I was more focused on entering the number and I did not realized the mistake.  My apologies  I will keep try to avoid those in my future posts. As for this its ok as it is in data. Converting the 'vnumber' into 'Nvisitn'  using the logic is key in this .

[ballardw]

---

@SASuserlot wrote:

Answer to your first question.

1. It doesn't not matter whatever the number after the decimal is, we only consider the number before the integer value. 1.09= 1,         2.05= 2 etc.

From the PICTURE you post 1.01 does not increment the count. You did not provide any rule for sequential 1's, sequential 2's and even more what if you have a 1.01, 2.01, 3? So you really should provide a RULE for how to use those. If they never occur that's one thing but until I have that as an actual statement of fact I am lacking information to code for when they happen.

This works for your example data:

data want;
   set visit;
   by id ;
   retain vcount;
   length visitn 8 nvisit $ 10;
   if first.id then do;
      vcount=1;
      visitn=1;
      nvisit= "Visit 1";
   end;
   else if index(Vnumber,'.')>0 then do;
   /* don't increment the counter and "visit" text*/
      visitn=input(vnumber,5.);
      nvisit="unsch";
   end;
   else do; 
      vcount+1;
      visitn =vcount;
      nvisit =catx(' ','Visit',put(vcount,best2.-L));
   end;
   drop vcount;
run;

The BY assumes the data is sorted by the ID. If not, then you need to sort By id and date before this data step.

When you use a By statement SAS supplies automatic variables that you test to see if a record is the first or last of a by group using the First. or Last. notation. The values are 1/0 and 1 is treated as True, zero as false. So you can reset values at the start of a group. The RETAIN keeps values across the data step boundary and is the typical tool for an incremented counter in the data step. Your EXCEPTION about the 1.01 and such mean that you have to test every value to see if it is an exception and if it is then do not increment the visit counter variable, Vcount and set a different Nvisit.

[SASuserlot]

Thank you. It worked for my major data too.   But have a question  you used vcount to guide the visitn. It worked because its in sequence. What is the solution if it is sequenced order. Probably you tried to explain this problem in your explanation May I could not catch it. Sorry. in the following data there is constant order vnumbers (1, 2,4,5, 7, 9, 11, 13, 15, 16 and these are scheduled meetings) for Id=004 which shows that this member present in every Meeting. Where  as  id=001  only attended up to 7 meetings.

data visit;
input ID $1-3 Vnumber $5-9 Svisit $10-14 nvisitn date ;
informat date ddmmyy10.;
format date date9.;
cards;
001	1	 VS1  1	 	10/10/2022
001	2	 VS2  2  	11/10/2022
001	4	 VS4  4		12/10/2022
001	5	 VS5  5		13/10/2022
001	7	 VS7  7 	14/10/2022
001	16	 END  9		15/10/2022
004	1	 VS1  1		10/10/2022
004	2	 VS2  2		11/10/2022
004	4	 VS4  4		13/10/2022
004	5	 VS5  5		16/10/2022
004	7	 VS7  7		17/10/2022
004	9	 VS9  9		18/10/2022
004	11	 VS11 11	20/10/2022
004	13	 VS13 13	22/10/2022
004 15	 VS15 15	27/10/2022
004 16	 END  16	28/10/2022
;
run;

How I can Change his next visit to  next scheduled number which is 9. Indirectly wherever vnumber =16 we need to check previous meeting number if its in order we dont' t change it otherwise map to next meeting number. like nvisitn in the pink below. Can we use a ' lag' concepts here?