PCT without Null category

Ronein — Tue, 08 Aug 2023 04:29:48 GMT

Hello

I want to calculate percentage of customers for each score category.

I want to perform 2 calculations:

1- percentage of customers for each score category

2-percentage of customers for each score category without score Null

What is the reason for error of second calculation?

How should I solve it?



Data have;
Input CustID Score Frame;
cards;
1 . .
2 11 100
3 2 200
4 2 150
5 2 80
6 2 300
7 3 400
8 4 0
9 5 0
10 5 400
11 5 500
12 5 600
13 6 150
14 . .
15 7 100
16 7 100
17 7 200
18 7 1000
19 8 200
20 10 300
;
Run;

proc sql;
select Score,
       count(*) as nr  format=comma23.,
	   sum(Frame)  as Total_Frame  format=comma23.,
	   calculated nr/(select count(*) as nr2 from have where Score is not null) as PCT1  format=percent8.1,
	   case when Score ne . then calculated nr else . end as nr1/(select count(*) as nr2 from have where Score is not null) as PCT2 format=percent8.1
from have
group by Score
;
quit;

Here is error Log

1                                                          The SAS System                               08:33 Monday, August 7, 2023

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='לקוחות_חסוי';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='K:\a.ריכוז תוכניות\CS\אד_הוק_לקוחות_חוסים\לקוחות_חסוי.sas';
9          %LET _SASPROGRAMFILEHOST='VSK2H010A3109';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         


27         Data have;
28         Input CustID Score Frame;
29         cards;

NOTE: Compression was disabled for data set WORK.HAVE because compression overhead would increase the size of the data set.
NOTE: The data set WORK.HAVE has 20 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              396.03k
      OS Memory           27808.00k
      Timestamp           08/08/2023 07:29:08 AM
      Step Count                        108  Switch Count  2
      Page Faults                       0
      Page Reclaims                     54
      Page Swaps                        0
      Voluntary Context Switches        23
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      
50         ;

51         Run;
52         
53         proc sql;
54         select Score,
55                count(*) as nr  format=comma23.,
2                                                          The SAS System                               08:33 Monday, August 7, 2023

56         	   sum(Frame)  as Total_Frame  format=comma23.,
57         	   calculated nr/(select count(*) as nr2 from have where Score is not null) as PCT1  format=percent8.1,
58         	   case when Score ne . then calculated nr else . end as nr1/(select count(*) as nr2 from have where Score is not null)
                                                                        _
______                                                                                                                              
                                                                        22
76
                                                                        200
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN, 
              LENGTH, TRANSCODE.  

ERROR 76-322: Syntax error, statement will be ignored.

ERROR 200-322: The symbol is not recognized and will be ignored.

58       ! 	   case when Score ne . then calculated nr else . end as nr1/(select count(*) as nr2 from have where Score is not null)
58       ! as PCT2 format=percent8.1
                   ______
                   22
ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING, ORDER, WHERE.  

59         from have
60         group by Score
61         ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
62         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              76.90k
      OS Memory           27808.00k
      Timestamp           08/08/2023 07:29:08 AM
      Step Count                        109  Switch Count  0
      Page Faults                       0
      Page Reclaims                     4
      Page Swaps                        0
      Voluntary Context Switches        0
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      
63         
64         
65         GOPTIONS NOACCESSIBLE;
66         %LET _CLIENTTASKLABEL=;
67         %LET _CLIENTPROCESSFLOWNAME=;
68         %LET _CLIENTPROJECTPATH=;
69         %LET _CLIENTPROJECTPATHHOST=;
70         %LET _CLIENTPROJECTNAME=;
71         %LET _SASPROGRAMFILE=;
72         %LET _SASPROGRAMFILEHOST=;
73         
74         ;*';*";*/;quit;run;
75         ODS _ALL_ CLOSE;
76         
77         
3                                                          The SAS System                               08:33 Monday, August 7, 2023

78         QUIT; RUN;
79

Re: PCT without Null category

Patrick — Tue, 08 Aug 2023 06:49:10 GMT

You can only use expressions on the source data which is before the AS.

Does below return what you're after?

proc sql;
select Score,
       count(*) as nr  format=comma23.,
	   sum(Frame)  as Total_Frame  format=comma23.,
	   calculated nr/(select count(*) as nr2 from have where Score is not null) as PCT1  format=percent8.1,
     (
	   case 
      when Score ne . then calculated nr 
      else . 
      end 
      ) /(select count(*) from have where Score is not null) as PCT2 format=percent8.1
from have
group by Score
;
quit;

topic Re: PCT without Null category in SAS Programming

PCT without Null category

Re: PCT without Null category