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

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
Opal | Level 21

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
Opal | Level 21

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
Obsidian | Level 7

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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