BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
melgwy
Calcite | Level 5

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,

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

5 REPLIES 5
Astounding
PROC Star

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
Calcite | Level 5
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
Super User

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
PROC Star

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
Calcite | Level 5
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. -##

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 8141 views
  • 1 like
  • 3 in conversation