Proc sql

[melgwy]

I have the following code where I use proc sql to identify readmits from claim data:

PROC SQL;
Create Table all.readmits2 as
Select A.uidnum, A.DISD label = "Previous Discharge Date",
B.ADMD label = "Admission Date",
B.ADMD-A.DISD as Gap, 1 as Tag
From all.hssc_ub__shp_visits_match as A join all.hssc_ub__shp_visits_match as B
On A.uidnum = B.uidnum
Where
B.ADMD-A.DISD between 1 and 30;
Quit;

The coding works well and I am able to identify the approiate 30 day readmits however, I would like to include important demographic/utlization variables in my sql table (all_sex, all_age, all_race, all_race2, cond3, all_ip, all_ed, all_op, all_setting,). When I try to inculde these additional variables, I run into issues.

I used the following code and recieved the error message posted below:

PROC SQL;
Create Table all.readmits4 as
Select A.uidnum, all_sex, all_age, all_race, all_race2, cond3, all_ip, all_ed, all_op, all_setting, 
A.DISD label = "Previous Discharge Date",
B.ADMD label = "Admission Date",
B.ADMD-A.DISD as Gap, 1 as Tag,
From all.hssc_ub__shp_visits_match as A join all.hssc_ub__shp_visits_match as B
On A.uidnum = B.uidnum
Where
B.ADMD-A.DISD between 1 and 30;
Quit;

Error Message:

199 From all.hssc_ub__shp_visits_match as A join all.hssc_ub__shp_visits_match as B
--- ----
22 76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /,
<, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT,
LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

199! From all.hssc_ub__shp_visits_match as A join all.hssc_ub__shp_visits_match as B
----
22
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT,
FROM, INFORMAT, INTO, LABEL, LEN, LENGTH, TRANSCODE.

How can Inculde the additional variables in my SQL table?

Thanks,

[Astounding]

I'm not a SQL expert, but I think it's useful to have another pair of eyes here.  Standard SQL syntax would remove the comma between Tag and From.

[melgwy]

Hi, So when I remove the comma, I get the following errors:

204 PROC SQL;
205 Create Table all.readmits4 as
206 Select A.uidnum, all_sex, all_age, all_race, all_race2, cond3, all_ip, all_ed,
206! all_op,all_setting,
207 A.DISD label = "Previous Discharge Date",
208 B.ADMD label = "Admission Date",
209 B.ADMD-A.DISD as Gap, 1 as Tag
210 From all.hssc_ub__shp_visits_match as A join all.hssc_ub__shp_visits_match as B
211 On A.uidnum = B.uidnum
212 Where
213 B.ADMD-A.DISD between 1 and 30;
ERROR: Ambiguous reference, column all_sex is in more than one table.
ERROR: Ambiguous reference, column all_age is in more than one table.
ERROR: Ambiguous reference, column all_race is in more than one table.
ERROR: Ambiguous reference, column all_race2 is in more than one table.
ERROR: Ambiguous reference, column cond3 is in more than one table.
ERROR: Ambiguous reference, column all_ip is in more than one table.
ERROR: Ambiguous reference, column all_ed is in more than one table.
ERROR: Ambiguous reference, column all_op is in more than one table.
ERROR: Ambiguous reference, column all_setting is in more than one table.
214 Quit;

Any thoughts?

[ballardw · Accepted solution]

Since those variables are in table A and B (reflexive joins are like that) prefix your "all" variables with A. just like you did for A.uidnum.

A.all_sex, A.all_age etc.

[Astounding]

Well, now that the first error is gone we can move on to the next one.  These messages are telling you (for example) that ALL_SEX is in both of your incoming data sets.  Which one do you want to select, the one in A or the one in B?  Your program has to specify that.  So instead of:

select all_sex,

You need to specify one of these:

select A.all_sex,

select B.all_sex,

You not only can choose, but you have to choose.

[melgwy]

Thank you....I'm running the revised code now to see if it works.

##- Please type your reply above this line. Simple formatting, no
attachments. -##