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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

4 REPLIES 4
Oligolas
Barite | Level 11

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 -

Zakharkou
Calcite | Level 5

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
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
Calcite | Level 5
It work great, thank You very much!!!

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 932 views
  • 1 like
  • 3 in conversation