topic group by - add row for missing groups for each group in SAS Programming

group by - add row for missing groups for each group

btbell — Tue, 09 Jan 2024 22:50:58 GMT

New to SAS. I'm updating an old report. I need to reshape the data a certain way so it can be uploaded/consumed. Would like to avoid going higher up through the process to rectify this as it will probably change the values.

In a nutshell, is there a way to insert a line with zero values for the variables, for groups missing a certain type.

I.e. i want all vehicle type groups to have all 3 types (car, van, suv). at this point, it could even be a proc print step if that's possible.

Thanks! Sorry for the brevity.

Re: group by - add row for missing groups for each group

SASKiwi — Tue, 09 Jan 2024 23:33:53 GMT

PROC MEANS in combination with the COMPLETETYPES and PRELOADFMT options should provide what you want. Check out this SAS doc example.

Re: group by - add row for missing groups for each group

Ksharp — Wed, 10 Jan 2024 09:31:36 GMT

https://blogs.sas.com/content/iml/2024/01/08/unobserved-levels-categorical.html

That would be better if you post some data.

Here is an example:

data have;
set sashelp.class;
run;


proc sql;
create table want as
select a.*,coalesce(weight,0) as weight,coalesce(height,0) as height
from (select * from (select distinct sex from have),(select distinct age from have)) as a
natural left join
(select sex,age,sum(weight) as weight from have group by sex,age)
natural left join
(select sex,age,sum(height) as height from have group by sex,age)
;
quit;

Re: group by - add row for missing groups for each group

btbell — Wed, 10 Jan 2024 14:56:54 GMT

Thanks for the responses so far. Let me explain this another way. The spreadsheet visual I included before is accurate of the data. It's a comprehensive table of obs that has been grouped by vehicle type. What i'm trying to add is

'for any grouping (car_type) i want it to have (car, van, suv), if van is missing, insert it' so ALL groupings have car, van, suv - in that order. If the var values are empty, that's fine i can handle that with missing option.

Re: group by - add row for missing groups for each group

LinusH — Wed, 10 Jan 2024 16:34:23 GMT

I think the options you been provided are relevant for your use case. Let us know if you don'tunderstand how to apply them.
You could get more hands-on help if you provide with programming steps that creates the report data set.

Re: group by - add row for missing groups for each group

A_Kh — Wed, 10 Jan 2024 23:32:59 GMT

Hi @btbell,

I had a similar (not exact) problem in the past and Community Experts (Ksharp) recommended to use SPARSE option in proc freq step which generates extra records for missing categories (per group) with 0 values.

Below is an example and hope this helps you to figure out your solution.

*Create data HAVE; 
data have; 
	set sashelp.cars; 
	if upcase(substr(make, 1, 1)) in ('A' 'B' 'C' 'D' 'E') then make= 'Var1';
	else if upcase(substr(make, 1, 1)) in ('F' 'G' 'H' 'I' 'J') then make= 'Var2';
	else if upcase(substr(make, 1, 1)) in ('K' 'L' 'M' 'N' 'O') then make= 'Var3';
	else make= 'Var4';
run;

*Calculate frequency counts for all categories, and creating null records for missing categories;  
proc freq data= have noprint;
	tables origin*make*type/out=have_wide missing sparse;
run; 

*Transposing the results to get the desired table structure; 
proc sort data=have_wide; by origin type make; run; 
proc transpose data=have_wide out=want (drop=_:);
	by origin type;
	var count;
	id make; 
run;

As you see in the result, we have all types per ORIGIN, even though it did not exist in the source data.

Re: group by - add row for missing groups for each group

btbell — Thu, 11 Jan 2024 03:37:50 GMT

Hi @A_Kh

@LinusH

@Ksharp

@SASKiwi

Thanks for you all's help. I've probably made things more complicated by the fact that i'm so new to SAS, to say there's a gap in my knowledge at this point is an understatement. There are so many ways this could probably be handled so let me start with a different question based on the data below - can i get all obs's numerical values onto one line (obs) in that order? The orig vars can be replaced and made to simply be var1...var18 ( i won't need the sec or id)

data have;
input sec $ id $ fr_m fr_f tot_;
cards;
06 M 10 8 0
06 D 0 12 120
06 To 8 18 20
06 PT 3 7 9
06 FT 53 41 9
06 To 7 79 13
;
run;

Want:

I need this out on a single line, e.g.

10 8 0 0 12 120 8 18 20 3 7 9 53 41 9 7 79 13

so my text file would be

data _null_;

	set have;
file "outfile.txt";

put @1 "xxxx" @5 "yyy" @7 "zzz" @11 var1 @16 var2 @21 var3 @26 var4 @31 var5 @36 var 6 @41 var 7 @46 var 8
	@51 var9 @56 var10 @61 var11 @66 var 12 @71 var 13 @76 var 14 @81 var 15 @86 var 
	@91 var 16 @96 var 17 @101 var 18 
run;

Thanks for your patience and any help is appreciated.

Re: group by - add row for missing groups for each group

Ksharp — Thu, 11 Jan 2024 05:51:27 GMT

So you want output to be a dataset or a txt file ?

data have;
input sec $ id $ fr_m fr_f tot_;
cards;
06 M 10 8 0
06 D 0 12 120
06 To 8 18 20
06 PT 3 7 9
06 FT 53 41 9
06 To 7 79 13
;
run;
proc iml;
use have;
read all var _num_ into x;
close;
want=rowvec(x);
create want from want;
append from want;
close;
quit;
proc export data=want outfile='c:\temp\outfile.txt' dbms=tab replace;run;

Re: group by - add row for missing groups for each group

btbell — Thu, 11 Jan 2024 17:14:17 GMT

SAS community is holding my post for approval. not sure what that's about. anyway, i actually, got it working. i found my mistakes in the reply post i sent you earlier but hasn't been released. thanks!