I'm trying to find how many people volunteered and want to link the hours volunteered by the same person in the last four weeks. If values is equal to (.), then no one came to volunteer and no hours registered. The data example shows the number of hours volunteered by a person on the first Monday (mon1) or Friday (fri1) to the fourth Monday (mon4) or Friday(fri4). The lastreg1-lastreg20 variables indicate when was the last day the person volunteered with 1 indicating last Monday and 5 indicate last Friday. These variables are also used to link the hours volunteered by the same person.
For example, if a person volunteered 10 hours on mon11 and 11 hours on mon16, then the lastreg16 is registered as 1 to indicate that this person also volunteered last Monday. If the value of mon11 is (.) hours, then search if this person volunteered the week before (mon6).
data example;
input mon1 tues1 wed1 thurs1 fri1 mon2 tues2 wed2 thurs2 fri2 mon3 tues3 wed3 thurs3 fri3 mon4 tues4 wed4 thurs4 fri4
lastreg1-lastreg20;
datalines;
10 12 13 . .
11 12 13 14 15
10 12 14 . .
11 14 15 16 17
. . . . .
1 2 3 . .
. . 3 . .
1 2 . . .
run;
In the example, there were 10 people who volunteered in the last four weeks. The hours registered by each person are shown in the data want.
data want;
input person1reg_1-person1reg_3 person2reg_1-person2reg_3 person3reg_1-person3reg_3 person4reg_1-person4reg_3
person5reg_1-person5reg_3 person6reg_1-person6reg_3 person7reg_1-person7reg_1 person8reg_1-person8reg_3
person9reg_1-person9reg_3 person10reg_1-person10reg_3;
datalines;
10 11 .
12 12 .
13 13 14
14 16 .
15 . .
10 11 .
12 14 .
15 . .
16 . .
17 . .
run;
Report (people are reading this) or dataset (goes into other processing or analysis)?
Your data structure is truly challenging. In your first example data set I find no information about any person. So how do we know how many people are involved? Or who did what? And I really don't understand what the output is supposed to have. If all of that PERSONX is supposed to be a specific person then an actual id is a better idea.
I would suggest going forward that data collected in the form of
PersonID Date Hoursworked
is much more flexible in the long run, especially when DATE contains the year, month and day of the month.
SAS has functions that will let you compare dates to determine intervals, days of weeks and when you want something for "last four weeks" it is possible to determine exactly which dates are involved.
I have a strong suspicion this is trying to translate a process from Excel or other spreadsheet that may be getting to cumbersome (as often happens) because you have to change processes each time you add a column to the "date" or a new person is involved.
Which is a poor way of structuring data.
Exactly the problem I am facing. People weren't assigned unique ids at the time of their service. The only data collected were lastreg variables.
I want to eventually find out how many people volunteered and assign unique ids to each. In that way, I don't have to deal with the lastreg. I can easily pull up the hours volunteered by individuals using their ids.
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 {i} icon or attached as text to show exactly what you have and that we can test code against.
I don't know what you pasted for the actual example data but there a many variables and very few values and possibly they do not align in any way, shape or fashion. The message windows will reformat text removing a lot of white space. So paste data step code in either a {I} or "Running man" opened code box.
You will likely have to provide a lot more walk through of how we are supposed to identify a person from those "reg" values. My head started hurting quite quickly contemplating the description. And a lack of concrete values to reference did not help.
Probably only need examples for 4 or 5 "people" and since intervals are mentioned something to start a base date from.
First of all, testing is no crime. So test your data step code and see if it produces the dataset you want, without any ERRORs or WARNINGs.
Right now, your "example" data step tries to read ~40 variables, but only 5 values are provided in each dataline. That won't work.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: