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;
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.
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.
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 .
@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.
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?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.