Help using Base SAS procedures

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

Reply
N/A
Posts: 0

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

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;
Super Contributor
Posts: 359

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

Posted in reply to deleted_user
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
SAS Super FREQ
Posts: 8,864

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

Posted in reply to deleted_user
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
Ask a Question
Discussion stats
  • 2 replies
  • 132 views
  • 0 likes
  • 3 in conversation