ERROR: Character expression requires a character format

dbcrow — Thu, 13 Apr 2023 00:31:10 GMT

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

Re: ERROR: Character expression requires a character format

SASKiwi — Thu, 13 Apr 2023 00:55:52 GMT

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;

Re: ERROR: Character expression requires a character format

dbcrow — Thu, 13 Apr 2023 01:00:32 GMT

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

topic Re: ERROR: Character expression requires a character format in SAS Programming

ERROR: Character expression requires a character format

Re: ERROR: Character expression requires a character format

Re: ERROR: Character expression requires a character format