ERROR: Character expression requires a character format

[dbcrow]

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):  

Alphabetic List of Variables and Attributes# Variable Type Len Format

2	county	Char	25	$25.
3	pop	Num	8	BEST12.
1	year	Num	8	4.

Alphabetic List of Variables and Attributes# Variable Type Len Format Informat

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.

Any help would be greatly appreciated!  

Best,

David

[SASKiwi · Accepted solution]

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;

[dbcrow]

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