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:
CREATE TABLE SASUSER.Query1_for_Cell_statistics_for_R AS SELECT CANCERS07_AGES_1.CODE FORMAT=CANCERC.,
FROM SASUSER.CANCERS07_AGES_1 AS CANCERS07_AGES_1
INNER JOIN EC100004.DANAH AS danah ON (CANCERS07_AGES_1.RES_CA = danah.Commarea);
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.,
8 danah.Commarea FORMAT=$2.,
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.
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
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.