topic Re: Adding information to imported data set. in New SAS User

Adding information to imported data set.

Sandeep77 — Tue, 31 May 2022 15:41:31 GMT

Hi Experts,

I have imported the data in SAS. Now I am trying to add few more information but I am getting error as unresolved reference. Please guide me as I am a new user.

proc sql;
create table Add_repcode AS
Select a.*,
b.rep_code
From work.acc_left_trace
inner join p2scflow.debt as b on a.debt_code = b.debt_code;
quit;

I am getting below error:

29         proc sql;
30         create table Add_repcode AS
31         Select a.*,
32         b.rep_code
33         From work.acc_left_trace
34         inner join p2scflow.debt as b on a.debt_code = b.debt_code;
ERROR: Unresolved reference to table/correlation name a.
ERROR: Could not expand a.*, correlation name not found.
ERROR: The following columns were not found in the contributing tables: a.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
35         quit;
NOTE: The SAS System stopped processing this step because of errors.

Re: Adding information to imported data set.

Tom — Tue, 31 May 2022 15:54:32 GMT

Your code is referring to table A, but you never told it which table was table A.

You only told it which table was table B.

inner join p2scflow.debt as b

How do you think you should change this line so that it defines a table reference of A?

From work.acc_left_trace

Re: Adding information to imported data set.

Sandeep77 — Tue, 31 May 2022 16:00:58 GMT

I am not very sure about it. I first imported a file and then created a table. Then I am trying to add the rep code information to those account number but it's not working.

proc sql;
create table Accounts_left_Trace AS
Select
account_code
From work.acc_left_trace; (acc_left_trace is the file that I mported in SAS)
quit;

proc sql;
create table Add_repcode AS
Select a.*,
b.rep_code
From work.acc_left_trace
inner join p2scflow.debt as b on a.debt_code = b.debt_code;
quit;

Re: Adding information to imported data set.

ballardw — Tue, 31 May 2022 16:13:04 GMT

proc sql;
create table Accounts_left_Trace AS
Select
account_code
From work.acc_left_trace; (acc_left_trace is the file that I mported in SAS)
quit;

proc sql;
create table Add_repcode AS
Select a.*,
b.rep_code
From work.acc_left_trace as A
inner join p2scflow.debt as b on a.debt_code = b.debt_code;
quit;

The underlined highlighted text assigns an alias of A to the data set Acc_left so you can use that in the A.debt_code or A.*.

You did that for the alias B, so I am very confused why not noticing it was not done for A.