SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
FelipeVerissim0
Fluorite | Level 6
i'm transcripting a T-SQL in SAS BASE language, and in a Stored Procedure i'm trying to create a table with proc sql and using a inner join in a created table named "tabelaC":
proc sql;
title 'TabelaC';
title2 'tabela T402CONT';
  create table tabelaC as
select 
nr_ctr,
nr_adl,
cd_emp,
cd_und,
sg_mod,
cd_cli,
qt_tit,
datepart(dt_ope) format=date9. as dt_ope,
vr_pcp
 
from STGCREDM.STG_T402CONT
order by cd_emp,cd_und,cd_cli,nr_ctr,nr_adl,sg_mod;
quit;

 

After this first step i'm trying to create a table "tabelaTIAB" with this following program:

proc sql;
title 'TabelaTIAB';
title2 'tabela T402TIAB';
  create table tabelaTIAB as
select 
nr_ctr,
datepart(dt_ven) format=date9. as dt_ven,
cd_cli,
nr_adl,
sg_mod
 
from STGCREDM.STG_T402TIAB as tiab
INNER JOIN (SELECT cd_cli, nr_ctr, nr_adl, sg_mod from work.tabelac) AS C ON 
tiab.cd_cli = c.cd_cli and tiab.nr_ctr = C.nr_ctr and tiab.nr_adl = C.nr_adl and tiab.sg_mod = C.sg_mod
order by cd_cli,nr_ctr,nr_adl,sg_mod;
quit;
 
I'm getting this following ERROR: Ambiguous reference, column nr_ctr is in more than one table.

 

27         
28         proc sql;
29         	title 'TabelaTIAB';
30         	title2 'tabela T402TIAB';
31           create table tabelaTIAB as
32         	select
33         	 nr_ctr,
34         	 datepart(dt_ven) format=date9. as dt_ven,
35         	 cd_cli,
36         	 nr_adl,
37         	 sg_mod
38         
39         	from STGCREDM.STG_T402TIAB as tiab
40         	INNER JOIN (SELECT cd_cli, nr_ctr, nr_adl, sg_mod from work.tabelac) AS C ON
41         	tiab.cd_cli = c.cd_cli and tiab.nr_ctr = C.nr_ctr and tiab.nr_adl = C.nr_adl and tiab.sg_mod = C.sg_mod
42         	order by cd_cli,nr_ctr,nr_adl,sg_mod;
ERROR: Ambiguous reference, column nr_ctr is in more than one table.

Can you guys help me? 

1 ACCEPTED SOLUTION

Accepted Solutions
sbxkoenk
SAS Super FREQ

Just make it a two-level-name (in your "order by" statement):

tiab.nr_ctr or C.nr_ctr

 

Koen

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

You are using nr_ctr in the ORDER statement, you have to specify which from which data set nr_ctr is to be used for this ordering.

 

Also, from now on, please post code using monospaced (also called fixed-width) fonts, that makes it easier to read. Here in the SAS communities, code should be placed into the "Insert SAS Code" window which appears when you click on the "little running man" icon.

--
Paige Miller
FelipeVerissim0
Fluorite | Level 6
proc sql;
	title 'TabelaTIAB';
	title2 'tabela T402TIAB';
  create table tabelaTIAB as
	select 
	 tiab.nr_ctr,
	 datepart(tiab.dt_ven) format=date9. as dt_ven,
	 tiab.cd_cli,
	 tiab.nr_adl,
	 tiab.sg_mod

	from STGCREDM.STG_T402TIAB as tiab
	INNER JOIN (SELECT cd_cli, nr_ctr, nr_adl, sg_mod from work.tabelac) AS C ON 
	tiab.cd_cli = c.cd_cli and tiab.nr_ctr = C.nr_ctr and tiab.nr_adl = C.nr_adl and tiab.sg_mod = C.sg_mod
	order by cd_cli,nr_ctr,nr_adl,sg_mod;
quit;

worked for me just putting the two-level-name in the columns in the SELECT statement. Thanks!

sbxkoenk
SAS Super FREQ

Just make it a two-level-name (in your "order by" statement):

tiab.nr_ctr or C.nr_ctr

 

Koen

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 2000 views
  • 0 likes
  • 3 in conversation