topic Re: Why wouldn't my COUNT() statement work in PROC SQL? in SAS Procedures

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

deleted_user — Wed, 28 Apr 2010 15:43:55 GMT

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;

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

Flip — Wed, 28 Apr 2010 16:04:18 GMT

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

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

Cynthia_sas — Wed, 28 Apr 2010 16:16:15 GMT

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