BookmarkSubscribeRSS Feed
dana_chicagohealth
Calcite | Level 5
Sorry if this has been posted before. I'm basically trying to add a variable called "count" to another table where I can then calculate some rates. The first table has 4 variables(code, res_ca, age, count) and the other table has 4 variables also(commarea, agegroup, pop2007, weight). I thought the join option under the "Filter and Query" task would fix my problem. I attempted to join the 2 tables by the variables res_ca and commarea and I got an syntax error: ERROR: Character expression requires a character format. Both variables are character and have a width of 2. I assume the error is referring to the join. I'm not sure. Is this the right task to accomplish what I'm trying to do or Should I be using something else? Any suggestions?

Below is the code generated from the join:

PROC SQL;
CREATE TABLE SASUSER.Query1_for_Cell_statistics_for_R AS SELECT CANCERS07_AGES_1.CODE FORMAT=CANCERC.,
CANCERS07_AGES_1.RES_CA FORMAT=$2.,
CANCERS07_AGES_1.AGE FORMAT=AGEES.,
CANCERS07_AGES_1.COUNT,
danah.Commarea FORMAT=$2.,
danah.Agegroup,
danah.pop2007,
danah.weight
FROM SASUSER.CANCERS07_AGES_1 AS CANCERS07_AGES_1
INNER JOIN EC100004.DANAH AS danah ON (CANCERS07_AGES_1.RES_CA = danah.Commarea);
QUIT;
9 REPLIES 9
ChrisHemedinger
Community Manager
Is CANCERS07_AGES_1.CODE a character or numeric variable, and is CANCERC. the correct format to apply?

You can change the properties of the columns you selected to remove the format or apply a different one, if necessary. I expect the FORMAT options (EG 4.1, correct?) are added automatically.

Chris
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
dana_chicagohealth
Calcite | Level 5
Chris,
CANCERS07_AGES_1.CODE is a numeric variable and CANCERC is a numeric format I created for the variable.
ChrisHemedinger
Community Manager
See these SAS notes to see if they apply:

http://support.sas.com/kb/6/239.html

http://support.sas.com/kb/6/783.html

http://support.sas.com/kb/7/665.html

http://support.sas.com/kb/15/136.html

Chris
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
dana_chicagohealth
Calcite | Level 5
Chris,
I looked at problem notes: 6239 and 6743. I corrected the columns in question and I still got the same error.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Suggest that you share the exact SAS log that is generating the error condition by pasting it in a post-reply for the most accurate feedback.

Scott Barry
SBBWorks, Inc.
dana_chicagohealth
Calcite | Level 5
Scott,
Here is the log generated from the join:



1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;


3 PROC SQL;
3 ! VALIDATE
4 SELECT CANCERS07_AGES_1.CODE FORMAT=CANCERC.,
5 CANCERS07_AGES_1.RES_CA FORMAT=$2.,
6 CANCERS07_AGES_1.AGE FORMAT=AGEES.,
7 CANCERS07_AGES_1.COUNT,
8 danah.Commarea FORMAT=$2.,
9 danah.Agegroup,
10 danah.pop2007,
11 danah.weight
12 FROM SASUSER.CANCERS07_AGES_1 AS CANCERS07_AGES_1
13 INNER JOIN EC100014.DANAH AS danah ON (CANCERS07_AGES_1.RES_CA = danah.Commarea);
ERROR: Character expression requires a character format.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
14 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

15
16 QUIT; RUN;
17
LinusH
Tourmaline | Level 20
It could be that your columns CODE and AGE which have numerical formats specified on them, are in fact char?

/Linus
Data never sleeps
dana_chicagohealth
Calcite | Level 5
Linus,
I checked both columns CODE and AGE and they are indeed numeric variables with numeric formats.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Suggest you analyze your code and verify that the appropriate FORMAT= "output formats" are also being applied to the different SAS variable types, those being either CHARACTER or NUMERIC, as the error is conveying. You may find a SAS CONTENTS report useful here for your SAS variables.

One approach would be to use "code commenting" to iteratively reduce your logic until the query works and then work to add back in (through uncommenting) your code-pieces in your PROC SQL query. With this approach, you will find that desk-checking will reveal the problem cause, and then come back to the forum for input/feedback.

Scott Barry
SBBWorks, Inc.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2121 views
  • 0 likes
  • 4 in conversation