cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chinna0369
Pyrite | Level 9 chinna0369
Pyrite | Level 9
Go to Solution

Show only Total column if they have zero counts

Posted 02-03-2021 01:03 PM (634 views)

Hello, 

 

I have data like below:

data have;
	input Obs 1-3 index 7-8 taval 13-14 tavalc $16-28        displaycol1 $29-39  displaycol2 $43-53 displaycol3 $55-65 tparamcd $69-78;
datalines;
 1    5     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 2    5     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 3    5     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 4    5     2  DOSE LEVEL 3 1 (  100.0% ) 0 ( 0.0%)   1 (  100.0% ) Visit 405 
 5    5     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 6    5    -1  Total        1( 100.0%)    0( 100.0%)  1( 100.0%)    Visit 405 
 7    6     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
 8    6     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
 9    6     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
10    6     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
11    6     1  DOSE LEVEL 4 1 (  100.0% ) 0 ( 0.0%)   1 (  100.0% ) Visit 406 
12    6    -1  Total        1( 100.0%)    0( 100.0%)  1( 100.0%)    Visit 406 
13    7     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 407 
14    7     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 407 
15    7     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 407 
16    7     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 407 
17    7     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 407 
18    7    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 407 
19    8     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 408 
20    8     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 408 
21    8     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 408 
22    8     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 408 
23    8     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 408 
24    8    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 408 
25    9     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 409 
26    9     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 409 
27    9     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 409 
28    9     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 409 
29    9     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 409 
30    9    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 409 
31   10     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 410 
32   10     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 410 
33   10     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 410 
34   10     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 410 
35   10     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 410 
36   10    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 410 
37   11     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 411 
38   11     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 411 
39   11     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 411 
40   11     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 411 
41   11     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 411 
42   11    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 411 
43   12     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 412 
44   12     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 412 
45   12     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 412 
46   12     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 412 
47   12     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 412 
48   12    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 412 
49   13     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 413 
50   13     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 413 
51   13     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 413 
52   13     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 413 
53   13     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 413 
54   13    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 413 
;
run;

I want to show all levels of each category if total row has any counts. Other if total row has only zero then I only want to show total row for that category. Is there any simple procedure for that?

 

For example below:

 

data have;
	input Obs 1-3 index 7-8 taval 13-14 tavalc $16-28        displaycol1 $29-39  displaycol2 $43-53 displaycol3 $55-65 tparamcd $69-78;
datalines;
 1    5     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 2    5     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 3    5     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 4    5     2  DOSE LEVEL 3 1 (  100.0% ) 0 ( 0.0%)   1 (  100.0% ) Visit 405 
 5    5     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 6    5    -1  Total        1( 100.0%)    0( 100.0%)  1( 100.0%)    Visit 405 
 7    6     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
 8    6     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
 9    6     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
10    6     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
11    6     1  DOSE LEVEL 4 1 (  100.0% ) 0 ( 0.0%)   1 (  100.0% ) Visit 406 
12    6    -1  Total        1( 100.0%)    0( 100.0%)  1( 100.0%)    Visit 406 
18    7    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 407 
14    8    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 408 
30    9    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 409 
36   10    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 410 
42   11    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 411 
48   12    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 412 
54   13    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 413 
;
run;

 

Thanks,

Adithya

0 Likes
1 ACCEPTED SOLUTION

Accepted Solutions
RichardDeVen
Barite | Level 11 RichardDeVen
Barite | Level 11

Re: Show only Total column if they have zero counts

Posted 02-03-2021 01:48 PM (592 views)  |   In reply to chinna0369

The taval input should be from columns 12-14, not 13-14

 

A SQL union can combine the 0 total results with the non-zero total group details.

 

Example:

 

proc sql;
  create table want as 
    select * from have where input(scan(displaycol1,1,'('),best12.) = 0 and taval=-1
  union 
    select * from have group by index
    having sum(input(scan(displaycol1,1,'('),best12.)) > 0
  order by
    obs
  ;
quit;

View solution in original post

0 Likes
5 REPLIES 5
Reeza
Super User Reeza
Super User

Re: Show only Total column if they have zero counts

Posted 02-03-2021 01:44 PM (596 views)  |   In reply to chinna0369

Filtering them out is straightforward. 

 

proc sql;
create table want as
select * 
from have t1
 where index not in (select index 
                                from have 
                                where taval = -1 and displaycol1= '0 ( 100.0%)') 
 and taval ne -1
order by 1, 2, 3 desc;
quit;

Note that your format for the data (space before parenthesis) appears to have changed so make sure your displaycol1 comparison matches your actual data as character comparisons are case and space sensitive.

 

Table 1

0 (  100.0%)

Table 2

0( 100.0%)
@chinna0369 wrote:

Hello, 

 

I have data like below:

data have;
	input Obs 1-3 index 7-8 taval 13-14 tavalc $16-28        displaycol1 $29-39  displaycol2 $43-53 displaycol3 $55-65 tparamcd $69-78;
datalines;
 1    5     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 2    5     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 3    5     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 4    5     2  DOSE LEVEL 3 1 (  100.0% ) 0 ( 0.0%)   1 (  100.0% ) Visit 405 
 5    5     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 6    5    -1  Total        1( 100.0%)    0( 100.0%)  1( 100.0%)    Visit 405 
 7    6     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
 8    6     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
 9    6     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
10    6     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
11    6     1  DOSE LEVEL 4 1 (  100.0% ) 0 ( 0.0%)   1 (  100.0% ) Visit 406 
12    6    -1  Total        1( 100.0%)    0( 100.0%)  1( 100.0%)    Visit 406 
13    7     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 407 
14    7     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 407 
15    7     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 407 
16    7     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 407 
17    7     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 407 
18    7    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 407 
19    8     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 408 
20    8     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 408 
21    8     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 408 
22    8     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 408 
23    8     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 408 
24    8    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 408 
25    9     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 409 
26    9     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 409 
27    9     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 409 
28    9     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 409 
29    9     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 409 
30    9    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 409 
31   10     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 410 
32   10     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 410 
33   10     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 410 
34   10     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 410 
35   10     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 410 
36   10    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 410 
37   11     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 411 
38   11     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 411 
39   11     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 411 
40   11     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 411 
41   11     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 411 
42   11    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 411 
43   12     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 412 
44   12     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 412 
45   12     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 412 
46   12     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 412 
47   12     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 412 
48   12    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 412 
49   13     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 413 
50   13     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 413 
51   13     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 413 
52   13     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 413 
53   13     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 413 
54   13    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 413 
;
run;

I want to show all levels of each category if total row has any counts. Other if total row has only zero then I only want to show total row for that category. Is there any simple procedure for that?

 

For example below:

 

data have;
	input Obs 1-3 index 7-8 taval 13-14 tavalc $16-28        displaycol1 $29-39  displaycol2 $43-53 displaycol3 $55-65 tparamcd $69-78;
datalines;
 1    5     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 2    5     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 3    5     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 4    5     2  DOSE LEVEL 3 1 (  100.0% ) 0 ( 0.0%)   1 (  100.0% ) Visit 405 
 5    5     1  DOSE LEVEL 4 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 405 
 6    5    -1  Total        1( 100.0%)    0( 100.0%)  1( 100.0%)    Visit 405 
 7    6     5  No Treatment 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
 8    6     4  DOSE LEVEL 1 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
 9    6     3  DOSE LEVEL 2 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
10    6     2  DOSE LEVEL 3 0 (  0.0%)    0 (  0.0%)  0 (  0.0%)    Visit 406 
11    6     1  DOSE LEVEL 4 1 (  100.0% ) 0 ( 0.0%)   1 (  100.0% ) Visit 406 
12    6    -1  Total        1( 100.0%)    0( 100.0%)  1( 100.0%)    Visit 406 
18    7    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 407 
14    8    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 408 
30    9    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 409 
36   10    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 410 
42   11    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 411 
48   12    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 412 
54   13    -1  Total        0( 100.0%)    0( 100.0%)  0( 100.0%)    Visit 413 
;
run;

 

Thanks,

Adithya

 

0 Likes
chinna0369
Pyrite | Level 9 chinna0369
Pyrite | Level 9

Re: Show only Total column if they have zero counts

Posted 02-03-2021 01:52 PM (589 views)  |   In reply to Reeza

Did you get the output what I am looking for below?

It is not wokring.

0 Likes
Reeza
Super User Reeza
Super User

Re: Show only Total column if they have zero counts

Posted 02-03-2021 03:38 PM (558 views)  |   In reply to chinna0369
You should also fix the N (%) for your totals, they're not the same as your other rows with the extra space.
0 Likes
Reeza
Super User Reeza
Super User

Re: Show only Total column if they have zero counts

Posted 02-03-2021 03:35 PM (558 views)  |   In reply to Reeza
That's partly because your example data set doesn't get generated correctly from your example data code. There are no -1 in the data for some reason. I did not check your code to ensure it worked correctly.
0 Likes
RichardDeVen
Barite | Level 11 RichardDeVen
Barite | Level 11

Re: Show only Total column if they have zero counts

Posted 02-03-2021 01:48 PM (593 views)  |   In reply to chinna0369

The taval input should be from columns 12-14, not 13-14

 

A SQL union can combine the 0 total results with the non-zero total group details.

 

Example:

 

proc sql;
  create table want as 
    select * from have where input(scan(displaycol1,1,'('),best12.) = 0 and taval=-1
  union 
    select * from have group by index
    having sum(input(scan(displaycol1,1,'('),best12.)) > 0
  order by
    obs
  ;
quit;
0 Likes

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • ‎02-03-2021 01:03 PM
  • 635 views
  • 0 likes
  • 3 in conversation