topic Ungrouoing the data in SAS Programming

Ungrouoing the data

Pooja98 — Thu, 01 Apr 2021 15:34:53 GMT

I came across a scenario for ungrouping the data.... but I don't know how to Handle this one

I don't have the exact dataset but I'll show the sashelp.class as example

The question contains
1. Group the sashelp.class dataset by using the variable age
2. Calculate the weight for the above dataset, then ungroup the data

How can I ungrouo the data?? Should I use the same variable which I used for group to ungroup these one.
What is the purpose of this?

Can anyone please help me to solve this?

TIA

Re: Ungrouoing the data

Reeza — Thu, 01 Apr 2021 15:36:31 GMT

What do you mean by "ungroup" the data?
That isn't a term I'm familiar with in SAS though it is used in R/Tidyverse.

Re: Ungrouoing the data

PaigeMiller — Thu, 01 Apr 2021 15:50:29 GMT

@Pooja98 wrote:

2. Calculate the weight for the above dataset, then ungroup the data

This part 2 makes no sense. What weight? What does "ungroup" mean in this context? Please explain further, and show an example.

Re: Ungrouoing the data

maguiremq — Thu, 01 Apr 2021 16:57:23 GMT

As others have noted, we need some more information, usually in the form of a 'have' and 'want' data set. The only thing I can imagine here is that you want to aggregate the average weight and then merge it back to the original information. That can be done this way, but I don't know if that's what you want:

/* Get mean weight by age */

proc means data = sashelp.class noprint;
	class age;
	var weight;
	ways 1;
	output out = work.class_grouped (drop = _:)
		mean(weight) = mean_weight;
run;

/* Sort by age to merge summary statistics back to original data set. */

proc sort data = sashelp.class out = work.class;
	by age;
run;

/* Merge summary statistics back to original data set. */

data work.class_all;
	merge	work.class 		(in = a)
			work.class_grouped 	(in = b);
	by		age;
			if a;
run;

/* work.class_grouped */

Obs Age mean_weight 
1 11 67.750 
2 12 94.400 
3 13 88.667 
4 14 101.875 
5 15 117.375 
6 16 150.000 

/* work.class_all */

Obs Name Sex Age Height Weight mean_weight 
1 Joyce F 11 51.3 50.5 67.750 
2 Thomas M 11 57.5 85.0 67.750 
3 James M 12 57.3 83.0 94.400 
4 Jane F 12 59.8 84.5 94.400 
5 John M 12 59.0 99.5 94.400 
6 Louise F 12 56.3 77.0 94.400 
7 Robert M 12 64.8 128.0 94.400 
8 Alice F 13 56.5 84.0 88.667 
9 Barbara F 13 65.3 98.0 88.667 
10 Jeffrey M 13 62.5 84.0 88.667 
11 Alfred M 14 69.0 112.5 101.875 
12 Carol F 14 62.8 102.5 101.875 
13 Henry M 14 63.5 102.5 101.875 
14 Judy F 14 64.3 90.0 101.875 
15 Janet F 15 62.5 112.5 117.375 
16 Mary F 15 66.5 112.0 117.375 
17 Ronald M 15 67.0 133.0 117.375 
18 William M 15 66.5 112.0 117.375 
19 Philip M 16 72.0 150.0 150.000

Re: Ungrouoing the data

Pooja98 — Thu, 01 Apr 2021 17:36:40 GMT

Ur guess is right, they gave some kind of information from R program and they need to convert to sas program

Can you tell me how to ungroup the data in the sas once it get grouped?

Re: Ungrouoing the data

Reeza — Thu, 01 Apr 2021 19:14:48 GMT

SAS doesn't have that same concept of grouping so it doesn't apply usually. It's part of how the data is stored in Tidyverse.

Re: Ungrouoing the data

maguiremq — Thu, 01 Apr 2021 20:05:31 GMT

Here's another attempt. It provides the same result as before, but I still don't know what you're looking for. To me, I read your question as: (1) I want to group_by (R lingo) a given variable (age), mutate or summarize a variable, then ungroup so I have the same data set as before with the summary statistic as a new column.

proc sql;
	select
				*
	from
				sashelp.class as a
					left join
				(select age, mean(weight) as mean_weight from sashelp.class group by age) as b
						on a.age = b.age;
quit;

Name Sex Age Height Weight Age mean_weight 
Thomas M 11 57.5 85 11 67.75 
Joyce F 11 51.3 50.5 11 67.75 
James M 12 57.3 83 12 94.4 
Louise F 12 56.3 77 12 94.4 
John M 12 59 99.5 12 94.4 
Jane F 12 59.8 84.5 12 94.4 
Robert M 12 64.8 128 12 94.4 
Barbara F 13 65.3 98 13 88.66667 
Jeffrey M 13 62.5 84 13 88.66667 
Alice F 13 56.5 84 13 88.66667 
Alfred M 14 69 112.5 14 101.875 
Judy F 14 64.3 90 14 101.875 
Henry M 14 63.5 102.5 14 101.875 
Carol F 14 62.8 102.5 14 101.875 
Mary F 15 66.5 112 15 117.375 
William M 15 66.5 112 15 117.375 
Janet F 15 62.5 112.5 15 117.375 
Ronald M 15 67 133 15 117.375 
Philip M 16 72 150 16 150

Re: Ungrouoing the data

Reeza — Thu, 01 Apr 2021 20:46:21 GMT

From what I understand, in tidyverse "Group" means that it considers the data by groups, so it's similar to a BY statement but it's stored on the data set as metadata, similar to how the SORT metadata is stored in SAS. It means data will be processed by group, even if the GROUP_BY isn't included. In SAS you'd need to explicitly include a BY statement for the same functionality.

OP I'm guessing you're using R terminology here (that I"m not 100% sure you understand) but it really doesn't apply.

You're more likely to get help or an answer by explaining what you have, by showing an example of your data, show us what you need as final output and show what you've tried. I suspect you're either looking for summary statistics or a way to merge summary statistics back into a data set, BUT as posted this question doesn't make sense from a SAS perspective.

Re: Ungrouoing the data

Reeza — Thu, 01 Apr 2021 20:47:07 GMT

And if you are looking to add summary stats to your main data set, SAS can obviously do that in many ways.
https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas