topic Re: Joining tables in SAS Enterprise Guide

Joining tables

dana_chicagohealth — Thu, 01 Apr 2010 19:36:17 GMT

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;

Re: Joining tables

ChrisHemedinger — Fri, 02 Apr 2010 11:24:15 GMT

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

Re: Joining tables

dana_chicagohealth — Mon, 05 Apr 2010 14:54:05 GMT

Chris,
CANCERS07_AGES_1.CODE is a numeric variable and CANCERC is a numeric format I created for the variable.

Re: Joining tables

ChrisHemedinger — Tue, 06 Apr 2010 13:27:56 GMT

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

Re: Joining tables

dana_chicagohealth — Tue, 06 Apr 2010 15:02:13 GMT

Chris,
I looked at problem notes: 6239 and 6743. I corrected the columns in question and I still got the same error.

Re: Joining tables

sbb — Tue, 06 Apr 2010 16:18:10 GMT

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.

Re: Joining tables

dana_chicagohealth — Tue, 06 Apr 2010 16:24:12 GMT

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

Re: Joining tables

LinusH — Wed, 07 Apr 2010 06:22:18 GMT

It could be that your columns CODE and AGE which have numerical formats specified on them, are in fact char?

/Linus

Re: Joining tables

dana_chicagohealth — Wed, 07 Apr 2010 15:02:15 GMT

Linus,
I checked both columns CODE and AGE and they are indeed numeric variables with numeric formats.

Re: Joining tables

sbb — Wed, 07 Apr 2010 15:59:00 GMT

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.