BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
dbcrow
Quartz | Level 8

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
2countyChar25$25.
3popNum8BEST12.
1yearNum84.

Alphabetic List of Variables and Attributes# Variable Type Len Format Informat
5countNum8BEST12.BEST32.
3dateNum8YYMMDD10. 
7highmmeNum8BEST12.BEST32.
2monyearChar6$6.$6.
4patcountyChar12$25.$12.
6tot_mmeNum8BEST12.BEST32.
1yearNum84. 
Any help would be greatly appreciated!  
 
Best,
David

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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;

 

View solution in original post

2 REPLIES 2
SASKiwi
PROC Star

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
Quartz | Level 8

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1208 views
  • 1 like
  • 2 in conversation