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
