## Automated query of a subgroup with generation of the total value for the subgroup

Dear SAS Community,

I have the following problem to solve. I have a data set in which the informations about the measurement sides are stored. The data set looks like this:

```id	subid	        side	        var1
1	1		1		3
1	2		2		3
1	3		2		3
2	1		2		5
2	2		0		5
3	1		1		4
3	2		0		4
3	3		1		4
3	4		1		4
3	5		1		4
4	1		0		0
5	1		1		1

Side
0=no value
1=left
2=right

var1
0=no value
1=one sided left
2=one sided right
3=both sides
4=one sided left and no value
5=one sided right and no value
6=both sides and no value```

How can I automate the function to query all the values of the variable "Side" and then save the result according to the attached coding for the total group.

1 ACCEPTED SOLUTION

Accepted Solutions  Ksharp
Super User

## Re: Automated query of a subgroup with generation of the total value for the subgroup

``````data have;
input id subid         side ;
cards;
1 1  1
1 2  2
1 3  2
2 1  2
2 2  0
3 1  1
3 2  0
3 3  1
3 4  1
3 5  1
4 1  0
5 1  1
;

proc sql;
create table want as
select *,
case
when sum(side=0)=count(*) then 0
when sum(side=1)=count(*) then 1
when sum(side=2)=count(*) then 2
when sum(side in (1 2))=count(*) then 3
when sum(side in (1 0))=count(*) then 4
when sum(side in (2 0))=count(*) then 5
when sum(side in (2 0 1))=count(*) then 6
else .
end as var1,
case
when sum(side=0)=count(*) then 'no value'
when sum(side=1)=count(*) then 'one sided left'
when sum(side=2)=count(*) then 'one sided right'
when sum(side in (1 2))=count(*) then 'both sides'
when sum(side in (1 0))=count(*) then 'one sided left and no value'
when sum(side in (2 0))=count(*) then 'one sided right and no value'
when sum(side in (2 0 1))=count(*) then 'both sides and no value'
else ' '
end as want length=40
from have
group by id;
quit;``````
4 REPLIES 4

## Re: Automated query of a subgroup with generation of the total value for the subgroup

Hi,

not really clear to me what you want to achieve.

Here's my best try using your description

``````PROC FORMAT;
VALUE Side
0='no value'
1='left'
2='right'
;
VALUE var1_
0='no value'
1='one sided left'
2='one sided right'
3='both sides'
4='one sided left and no value'
5='one sided right and no value'
6='both sides and no value'
;
run;

DATA have;
INPUT id	subid side var1;
FORMAT side side. var1 var1_.;
DATALINES;
1	1		1		3
1	2		2		3
1	3		2		3
2	1		2		5
2	2		0		5
3	1		1		4
3	2		0		4
3	3		1		4
3	4		1		4
3	5		1		4
4	1		0		0
5	1		1		1
;
RUN;

PROC SQL;
CREATE TABLE want AS
SELECT DISTINCT side,var1
FROM have
ORDER BY side,var1
;
QUIT;

``````
________________________

- Cheers -

## Re: Automated query of a subgroup with generation of the total value for the subgroup

The issue is that I need to create the variable "var1" according to the above description. Unfortunately the var1 is not present.  Ksharp
Super User

## Re: Automated query of a subgroup with generation of the total value for the subgroup

``````data have;
input id subid         side ;
cards;
1 1  1
1 2  2
1 3  2
2 1  2
2 2  0
3 1  1
3 2  0
3 3  1
3 4  1
3 5  1
4 1  0
5 1  1
;

proc sql;
create table want as
select *,
case
when sum(side=0)=count(*) then 0
when sum(side=1)=count(*) then 1
when sum(side=2)=count(*) then 2
when sum(side in (1 2))=count(*) then 3
when sum(side in (1 0))=count(*) then 4
when sum(side in (2 0))=count(*) then 5
when sum(side in (2 0 1))=count(*) then 6
else .
end as var1,
case
when sum(side=0)=count(*) then 'no value'
when sum(side=1)=count(*) then 'one sided left'
when sum(side=2)=count(*) then 'one sided right'
when sum(side in (1 2))=count(*) then 'both sides'
when sum(side in (1 0))=count(*) then 'one sided left and no value'
when sum(side in (2 0))=count(*) then 'one sided right and no value'
when sum(side in (2 0 1))=count(*) then 'both sides and no value'
else ' '
end as want length=40
from have
group by id;
quit;``````

## Re: Automated query of a subgroup with generation of the total value for the subgroup

It work great, thank You very much!!!
Discussion stats
• 4 replies
• 356 views
• 1 like
• 3 in conversation