DATA Step, Macro, Functions and more

help in proc sql statement

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

help in proc sql statement

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;


Accepted Solutions
Solution
‎05-05-2017 09:40 AM
Super User
Posts: 7,768

Re: help in proc sql statement

Posted in reply to alexdsa310

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 5,426

Re: help in proc sql statement

Posted in reply to alexdsa310

And why is that?

Data never sleeps
Super User
Super User
Posts: 7,942

Re: help in proc sql statement

Posted in reply to alexdsa310

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.

Contributor
Posts: 41

Re: help in proc sql statement

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;

 

Super User
Posts: 7,768

Re: help in proc sql statement

Posted in reply to alexdsa310

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 41

Re: help in proc sql statement

Posted in reply to KurtBremser

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.

Solution
‎05-05-2017 09:40 AM
Super User
Posts: 7,768

Re: help in proc sql statement

Posted in reply to alexdsa310

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,942

Re: help in proc sql statement

Posted in reply to alexdsa310

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.

Super User
Posts: 7,768

Re: help in proc sql statement

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,768

Re: help in proc sql statement

Posted in reply to alexdsa310

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 137 views
  • 1 like
  • 4 in conversation