Hi, folks-
I'm getting this error when I try a left join in PROC SQL: ERROR: Character expression requires a character format.
The solutions for this error involve making sure that the "on" variables in the join are both string variables. They are, but I'm still getting the error.
Here's the code:
proc sql;
create table ucd4 as
select a.monyear format yymon.,
a.patcounty as county,
a.year,
a.count,
a.tot_mme as mme,
a.highmme,
b.county,
b.year,
b.pop
from ucd3 as a
left join pop as b
on a.year = b.year
and upcase(a.patcounty) = upcase(b.county);
quit;
And here's the complete log:
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='ucd_cures_indicators';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='G:\SAPB\Projects\OD2A\Evaluation\UCD Evaluation\Data\ucd_cures_indicators.egp';
6 %LET _CLIENTPROJECTPATHHOST='MXL1193B73';
7 %LET _CLIENTPROJECTNAME='ucd_cures_indicators.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 proc sql;
27 create table ucd4 as
28 select a.monyear format yymon.,
29 a.patcounty,
30 a.year,
31 a.count,
32 a.tot_mme as mme,
33 a.highmme,
34 b.county,
35 b.year,
36 b.pop
37 from ucd3 as a
38 left join pop as b
39 on a.year = b.year
40 and upcase(a.patcounty) = upcase(b.county);
ERROR: Character expression requires a character format.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
41 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
42
43 GOPTIONS NOACCESSIBLE;
44 %LET _CLIENTTASKLABEL=;
45 %LET _CLIENTPROCESSFLOWNAME=;
46 %LET _CLIENTPROJECTPATH=;
47 %LET _CLIENTPROJECTPATHHOST=;
48 %LET _CLIENTPROJECTNAME=;
49 %LET _SASPROGRAMFILE=;
50 %LET _SASPROGRAMFILEHOST=;
51
52 ;*';*";*/;quit;run;
53 ODS _ALL_ CLOSE;
54
55
56 QUIT; RUN;
I did proc contents for both tables "a" (pop) and "b" (ucd3):
2 | county | Char | 25 | $25. |
3 | pop | Num | 8 | BEST12. |
1 | year | Num | 8 | 4. |
5 | count | Num | 8 | BEST12. | BEST32. |
3 | date | Num | 8 | YYMMDD10. | |
7 | highmme | Num | 8 | BEST12. | BEST32. |
2 | monyear | Char | 6 | $6. | $6. |
4 | patcounty | Char | 12 | $25. | $12. |
6 | tot_mme | Num | 8 | BEST12. | BEST32. |
1 | year | Num | 8 | 4. |
Looks like you have a syntax error using the FORMAT option and you can't apply a numeric date format to a character variable. Try this:
proc sql;
create table ucd4 as
select a.monyear,
a.patcounty as county,
a.year,
a.count,
a.tot_mme as mme,
a.highmme,
b.county,
b.year,
b.pop
from ucd3 as a
left join pop as b
on a.year = b.year
and upcase(a.patcounty) = upcase(b.county);
quit;
Looks like you have a syntax error using the FORMAT option and you can't apply a numeric date format to a character variable. Try this:
proc sql;
create table ucd4 as
select a.monyear,
a.patcounty as county,
a.year,
a.count,
a.tot_mme as mme,
a.highmme,
b.county,
b.year,
b.pop
from ucd3 as a
left join pop as b
on a.year = b.year
and upcase(a.patcounty) = upcase(b.county);
quit;
Hi, SASKiwi-
Thanks for responding to another of my inquiries. It's almost like you're my personal SAS assistant! 😉
Worked like a charm!
I'm wondering why SAS throws that error message. It makes me think that the problem is the join variables rather than the format.
At any rate, I tender my heartfelt gratitude!
David
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.