help in proc sql statement

[alexdsa310]

i am extracting a part from the variables in sv dataset in order to merge with _libmes_ dataset which are common but i am not able use the below condition. 

can anyone help if the below code

proc sql;
create table _c01_ as
select dequote(scan(orion,-3,".")) as a.sid, dequote(scan(orion,-2,".")) as a.mame,
dequote(scan(orion,-1,".")) as a.name, a.oriion,a.ORION
from sv a left join _libmes_ b
on a.sid=b.sid and a.mame=b.mame;
quit;

[LinusH]

And why is that?

[RW9]

Sorry, so what is it that "not able use the below condition." isnt working?  Refer to the guidance under the Post button when you make a post - post test data in the form of a datastep, what the output should look like, what code you have and log with errors/warnings, and a brief explanation.

[alexdsa310]

sorry for not being clear. i am using the below code and it works just thought if it can fit in a single sql table. If not i can use the below.

Will provide sample data if this is not clear.

data _c0_;
set sv;
sid=dequote(scan(origin,-3,"."));
mme=dequote(scan(orion,-2,"."));
nme=dequote(scan(orion,-1,"."));
run;

proc sql;
create table _c01_ as
select a.*,b.lime from _c0_ a left join _limes_ b
on a.sid=b.sid ;
quit;

[Kurt_Bremser]

And what exactly happens when you run

proc sql;
create table _c01_ as
select dequote(scan(orion,-3,".")) as a.sid, dequote(scan(orion,-2,".")) as a.mame,
dequote(scan(orion,-1,".")) as a.name, a.oriion,a.ORION
from sv a left join _libmes_ b
on a.sid=b.sid and a.mame=b.mame;
quit;

?

Don't force us to pry information out of your nose.

[alexdsa310]

sorry once again. i posted the error message but somehow it didnt make it.

this error comes in  a. part

as a.sid

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT,
FROM, INFORMAT, INTO, LABEL, LEN, LENGTH, TRANSCODE.

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

[Kurt_Bremser · Accepted solution]

You can't use table aliases in the "as" part of the select; you are declaring a new name there, and that belongs to the output table, where no table designator is needed and is therefore not allowed.

Use

as sid

instead.

[RW9]

I hadn't spotted this at first glance - good example for good code formatting.

proc sql;
  create table _C01_ as
  select  dequote(scan(ORION,-3,".")) as A.SID, 
          dequote(scan(ORION,-2,".")) as A.MAME,
          dequote(scan(ORION,-1,".")) as A.NAME, 
          A.ORIION,
          A.ORION
  from    SV A 
  left join _LIBMES_ B
  on      A.SID=B.SID 
  and     A.MAME=B.MAME;
quit;

In the select statement you are selecting variables as <alis>.<variable> as select is creating the new variable, it can't have an alias.  Remove the A. from each of these.  Then if it still doesn't work, post full code and full log.

[Kurt_Bremser]

Once again this underlines the importance of Maxim 12, and the dreadful consequences of spaghetti coding.

[Kurt_Bremser]

You should know by now (29 posts on communities.sas.com) that you need to be more precise in formulating your question. If an ERROR or WARNING happens, posting the log is an absolute must!

If a semantic problem exists (result does not match expectations), give a thorough description of that. If in doubt, post example data (datastep!).

"I have a problem" or "does not work" is totally useless.