This response summarizes the excellent feedback provided by Kurt_Bremser, PaigeMiller, sbxkoenk, and ballardw in previous comments. First, some code to create the input table and demonstrate the original problem:
data BEO;
infile datalines truncover;
input ccsid:$10. date:mmddyy10. CLNT_BEO_SEG_CDE:$20.;
datalines;
L100 02/21/2022 Lurker
E101 03/10/2022 Engager
D102 04/10/2022 Disengaged
E104 05/12/2022 Engager
E105 05/12/2022 Engager
E106 05/12/2022 Engager
;
proc sql;
create table Engager as
select date, CLNT_BEO_SEG_CDE, count(*) as count
from BEO
where put(ccsid, 25.) ne ' '
and CLNT_BEO_SEG_CDE = 'Engager'
group by date, CLNT_BEO_SEG_CDE
order by count
;
quit;
Log:
ERROR: Numeric format F in the PUT function requires a numeric argument.
NOTE: The SAS System stopped processing this step because of errors.
The error message indicates that the PUT function call is trying to apply a numeric format (25.) to a character argument (cssid). When testing for missing values, it’s better to use syntax that doesn’t require you to detect and/or convert argument data types. Your original expression:
where put(ccsid, 25.) ne ' '
would be better expressed using either the “is not missing” WHERE statement syntax, or using the MISSING function, which works in any expression. Both expressions will produce consistent results for both character and numeric values:
where ccsid is not missing
where not missing(ccsid)
You reported getting errors even after eliminating the PUT function, but I could not replicate any condition where the expression still produced an error and you did not provide a copy of the code. This solution, originally supplied by Kurt_Bremser, runs without error and produces the correct result set:
proc sql;
create table Engager as
select date, CLNT_BEO_SEG_CDE, count(*) as count
from BEO
where ccsid ne ' '
and CLNT_BEO_SEG_CDE = 'Engager'
group by date, CLNT_BEO_SEG_CDE
order by count
;
quit;
Log:
NOTE: Table WORK.ENGAGER created, with 2 rows and 3 columns.
This proposed alternative solution also ran without error and produced the correct results:
proc sql;
create table Engager as
select date, CLNT_BEO_SEG_CDE , count(*) as count
from BEO
where not missing(ccsid)
and CLNT_BEO_SEG_CDE = 'Engager'
group by date, CLNT_BEO_SEG_CDE
order by count
;
quit;
Log:
NOTE: Table WORK.ENGAGER created, with 2 rows and 3 columns.
May the SAS be with you!
Mark
... View more