Pyrite | Level 9

## Ratio of values based on another dataset

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?

6 REPLIES 6
PROC Star

## Re: Ratio of values based on another dataset

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Pyrite | Level 9

## Re: Ratio of values based on another dataset

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
PROC Star

## Re: Ratio of values based on another dataset

@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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Pyrite | Level 9

## Re: Ratio of values based on another dataset

ok I've got it.
Diamond | Level 26

## Re: Ratio of values based on another dataset

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.

--
Paige Miller
Pyrite | Level 9

## Re: Ratio of values based on another dataset

Sorry, you are right. I edited the post
Discussion stats
• 6 replies
• 813 views
• 0 likes
• 3 in conversation