Why wouldn't my COUNT() statement work in PROC SQL?

[deleted_user]

It will run without the COUNT() statement but with it there, it won't run. It's so frustrating; I can't figure out why! TMISSING variable has either 'YES' or 'NO' and I want to count how many YES's per cycle per person (ID_NUM).

PROC SQL;
CREATE TABLE SUBJECT AS
SELECT DISTINCT ID_NUM AS ID, CYCLE LENGTH=5, TMISSING LENGTH=8,
COUNT(DISTINCT TMISSING='YES') AS COUNT LENGTH=5
FROM STACKED_DENTAL
GROUP BY ID_NUM, CYCLE;
QUIT;
PROC PRINT DATA=SUBJECT;
RUN;

[Flip]

COUNT(DISTINCT TMISSING='YES') should return 2 since the distinct values of the logical result of (TMISSING='YES') would be True and False. so 2 distinct values. SUM(DISTINCT TMISSING='YES') should give you what you want


Message was edited by: Flip

[Cynthia_sas]

For simple counting reports, I always turn to PROC FREQ. For example, if I want to know how many products were sold for each REGION in SASHELP.SHOES, I can do this, using BY group processing:
[pre]
proc sort data=sashelp.shoes out=shoes;
by region;
run;

ods html file='c:\temp\shoe_count.html' style=sasweb;
proc freq data=shoes;
by region;
table product;
run;
ods html close;
[/pre]

Just as you can have more variables listed in a GROUP BY for PROC SQL, you can have more than one BY variable used for BY group processing. So your PROC FREQ could have BY ID_NUM CYCLE for your PROC FREQ.

cynthia