topic Re: Ratio of values based on another dataset in New SAS User

Ratio of values based on another dataset

NewUsrStat — Mon, 28 Aug 2023 15:48:35 GMT

Hi guys,

suppose to have the following table:

dataset 1

Region	Value	Class
1	34	0
2	23	0
3	12	0
1	65	1
2	32	1
3	43	1
1	98	2
2	76	2
3	44	2
1	1	3
2	2	3
3	3	3
1	45	4
2	38	4
3	43	4
1	0	5
2	0	5
3	0	5

data dataset1; input Region Value Class $; cards; 1 34 0 2 23 0 3 12 0 1 65 1 2 32 1 3 43 1 1 98 2 2 76 2 3 44 2 1 1 3 2 2 3 3 3 3 1 45 4 2 38 4 3 43 4 ;

Then you have another dataset as follows:

dataset 2

ID	Region
1	1
2	1
3	3
4	2
5	2
6	3
7	1
8	2
9	1
10	1
11	1
12	3
13	3
14	3
15	2
16	1
17	2
18	3

data dataset2; input ID Region $; cards; 1 1 2 1 3 3 4 2 5 2 6 3 7 1 8 2 9 1 10 1 11 1 12 3 13 3 14 3 15 2 16 1 17 2 18 3 ;

Then you want to divide each value of the column "Value" of the first dataset by the total number of corresponding "Region" class in the second dataset. Lets say: 34 will be divided by 7 because in dataset 2 there are 7 IDs from region = 1. 23 in dataset 1 will be divided by 5 because there are 5 IDs in dataset 2 with "Region" = 2. This, will be done in dataset 1 regardless of the "Class" variable. It will be there and will not take any part into the calculation. Can anyone help me please?

Thank you in advance

Re: Ratio of values based on another dataset

mkeintz — Mon, 28 Aug 2023 14:04:33 GMT

OK - first things first:

How do you propose to get the frequency of REGION in dataset 2? There are many ways, but I suggest taking a look at PROC FREQ and the OUT option of the TABLE statement.

Once you have done that, then you can consider accessing those frequencies while processing dataset 1.

Re: Ratio of values based on another dataset

PaigeMiller — Mon, 28 Aug 2023 14:52:11 GMT

Hello, @NewUsrStat

I previously helped you, and requested that in all future threads, you provide data as working SAS data step code, and I provided a simple example. Please do that. We're trying to help you, but you have to help us too.

Re: Ratio of values based on another dataset

NewUsrStat — Mon, 28 Aug 2023 15:49:01 GMT

Sorry, you are right. I edited the post

Re: Ratio of values based on another dataset

NewUsrStat — Mon, 28 Aug 2023 19:48:13 GMT

Thank you mkeintz. Yes, the idea is to use proc freq but I would like to do all in the same piece of code, let say do the ratio while calculating the "unverses" in dataset2

Re: Ratio of values based on another dataset

mkeintz — Mon, 28 Aug 2023 20:44:46 GMT

@NewUsrStat wrote:
Thank you mkeintz. Yes, the idea is to use proc freq but I would like to do all in the same piece of code, let say do the ratio while calculating the "unverses" in dataset2

By "same piece of code" do you mean a single data or proc step? Or just a PROC followed by another proc or data step in a single program?

Oh yes, and why do you define REGION as a numberic variable in one dataset and character in the other? How do you expect to match the needed counts for character-variable REGION in dataset2 with numeric REGION in dataset1?

Make yourself the master of this task, not the other way around. That starts with clearly and comprehensively stating the problem, with vetted sample data.

Re: Ratio of values based on another dataset

NewUsrStat — Mon, 28 Aug 2023 20:50:44 GMT

ok I've got it.