This is entirely made up because I would break the law with real data. There is a comma after each field. I am showing rows of data in this made up example. In reality there are thousands of cases most with many rows of course. There are 4 fields in each row. The first element before the comma is thename of the field, the second part before each comma is the value in that field (the name of the field is bolded). The 4th field is the dependent variable.
Caseid 1,
Spending on service $100,
Count of services 2,
Service type 'Work Evaluation',
Income 2 quarters after closure $400
Caseid 1,
Spending on service $300,
Count of service 1,
Service type 'Transportation',
Income 2 quarters after closure $400
Caseid 2,
Spending on service $700,
Count of service 2,
Service type 'Transportation',
Income 2 quarters after closure $700
Caseid 3,
Spending on service $500,
Count of service 2,
Service type 'Self-Employment Services',
Income 2 quarters after Closure $1000
Caseid 3,
Spending on service $700,
Count of service 1,
Service type 'WIPA Services',
Income 2 quarters after Closure $1000
Caseid 3,
Spending on service $300,
Count of service 2,
Service type 'Transportation',
Income 2 quarters after closure $1000
So in this example caseid 3 got 3 types of services 'Self-Employment Services','WIPA Services' and Transportation' for each they had a specific number of services (the count of service) and spent a specific total amount on those services (the total spending on a service). Two quarters after closure the case earned a specific amount (the Income 2 quarters after closure). Since it is the same case that amount will always be the same for a given case regardless of the number of rows they have.
The theory, or what we are testing, is that the amount we spend in a particular type of service and/or the count of services drives income 2 quarters after closure. I think I have to recode the data to show this, but I am not sure how.
I think we have to collapse the 3 predictor fields spending, counts and service type into two counts per service type and spending per service type, but for the life of me I can't think of how
Some type of proc sql I guess? I am not sure what the fields should look like.
Or maybe there is a way to do this in proc reg, grouping the regression by Service type maybe?
