Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Automated query of a subgroup with generation of the total value for t...

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 04-04-2023 03:10 AM
(355 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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 -

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

It work great, thank You very much!!!

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

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.