BookmarkSubscribeRSS Feed
NewUsrStat
Quartz | Level 8

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

6 REPLIES 6
mkeintz
Jade | Level 19

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

--------------------------
NewUsrStat
Quartz | Level 8
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
mkeintz
Jade | Level 19

@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

--------------------------
NewUsrStat
Quartz | Level 8
ok I've got it.
PaigeMiller
Diamond | Level 26

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
NewUsrStat
Quartz | Level 8
Sorry, you are right. I edited the post

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

From SAS Users blog
Want more? Visit our blog for more articles like these.
5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 319 views
  • 0 likes
  • 3 in conversation