topic Re: count(*) in SAS Programming

count(*)

HeatherNewton — Thu, 01 Dec 2022 02:43:24 GMT

PROC SQL;
   create table summary_ead_&p_yymmdd as
   select portfolio, product, ead,
       sum(ccf*rel_cnt)/sum(rel_cnt) as dcwccf
       avg(ccf) as dawccf
       count(*) as cnt
   from acct_list_final_&P_yymmdd
where ead in ('PHI','ALP')
   group by portfolio, product, ead,
quit;

cnt= count(*) - no of rows in dataset

but there is a clause 'where ead in ('PHI', 'ALP')'

so does count(*) include only those fulfilling where ead in ('PHI', 'ALP') or

all rows in dataset acct_list_final_&P_yymmdd regardless of where statement?

Re: count(*)

Astounding — Thu, 01 Dec 2022 02:59:51 GMT

Only rows with the right values for ead get counted. The same applies across the board. Only rows with the right values for ead get used to compute sums, means, counts.

Re: count(*)

HeatherNewton — Thu, 01 Dec 2022 03:36:22 GMT

So for each row cnt=1 or cnt=total no of row in whole table fulfiling the ead condition?

Re: count(*)

ballardw — Thu, 01 Dec 2022 04:07:17 GMT

@HeatherNewton wrote:
PROC SQL;
   create table summary_ead_&p_yymmdd as
   select portfolio, product, ead,
       sum(ccf*rel_cnt)/sum(rel_cnt) as dcwccf
       avg(ccf) as dawccf
       count(*) as cnt
   from acct_list_final_&P_yymmdd
where ead in ('PHI','ALP')
   group by portfolio, product, ead,
quit;
 
cnt= count(*) - no of rows in dataset

but there is a clause 'where ead in ('PHI', 'ALP')'

so does count(*) include only those fulfilling where ead in ('PHI', 'ALP') or

all rows in dataset acct_list_final_&P_yymmdd regardless of where statement?

If that is the actual code you submitted the comma after Ead in the Group by is going to cause problems. See this LOG from similar code:

109  proc sql;
110     select sex,age, mean(height) as mheight
111     from sashelp.class
112     group by sex,age,
113  quit;
ERROR: The following columns were not found in the contributing tables: quit.

So your output could have nothing because the comma before Quit isn't going to allow anything much to happen.

Re: count(*)

HeatherNewton — Thu, 01 Dec 2022 05:37:31 GMT

sorry it should be a semi-colon

assuming no code typo, what should count(*) for each row?

Re: count(*)

Kurt_Bremser — Thu, 01 Dec 2022 06:31:43 GMT

COUNT(*) counts all rows; if a GROUP BY is present, the summary functions (of which COUNT is one) will calculate for each group, otherwise for the whole dataset.