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

[Zakharkou]

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.

Thanks in advance for your help.

[Oligolas]

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;

[Zakharkou]

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

[Ksharp · Accepted solution]

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;

[Zakharkou]

It work great, thank You very much!!!