BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
NewUsrStat
Quartz | Level 8

Hi guys, 

Suppose to have a dataset: 

 ID       Class        Place

  1            0               1

  2           0               1

  3           0               1

  4           1                2

  5           2               2

  6           2               2

.....        .....             .....

 

 

Then, you have another dataset of which you will take only the total number of rows, for example 10. 

Now you want to divide the number of IDs in the first dataset by the total number of IDs in the second dataset, i.e., 10 but you want to do this by "Class" and "Place". How, this, can be achieved? In other words the desired output would be: 

 

Place Class Count Ratio
1 0 3 0.3
1 1 0 0
1 2 0 0
2 0 0 0
2 1 1 0.1
2 2 2 0.2

 

As you can see (as an example), for place =1, there are IDs only in Class 0 and they are 3 in total. So 3/10 = 0.3. These are example numbers of the real situation. Totally I have 10 "Classes", 1 "Place" and 19.398 rows in the first dataset and 76.984 rows in the second dataset. 

 

Thank you in advance

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

For problems that involve counting, your first choice ought to be PROC FREQ

 

proc freq data=have;
	tables class*place/noprint list sparse out=counts;
run;
data want;
    set counts(drop=percent);
    ratio=count/10;
run;

 

 

From now on, please provide data as WORKING data step code, for example:

 

data have;
input ID Class Place;
cards;
  1            0               1
  2           0               1
  3           0               1
  4           1                2
  5           2               2
  6           2               2
;

 

--
Paige Miller

View solution in original post

1 REPLY 1
PaigeMiller
Diamond | Level 26

For problems that involve counting, your first choice ought to be PROC FREQ

 

proc freq data=have;
	tables class*place/noprint list sparse out=counts;
run;
data want;
    set counts(drop=percent);
    ratio=count/10;
run;

 

 

From now on, please provide data as WORKING data step code, for example:

 

data have;
input ID Class Place;
cards;
  1            0               1
  2           0               1
  3           0               1
  4           1                2
  5           2               2
  6           2               2
;

 

--
Paige Miller

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 339 views
  • 1 like
  • 2 in conversation