topic Re: help in proc sql statement in SAS Programming

help in proc sql statement

alexdsa310 — Fri, 05 May 2017 13:02:56 GMT

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;

Re: help in proc sql statement

LinusH — Fri, 05 May 2017 13:13:46 GMT

And why is that?

Re: help in proc sql statement

RW9 — Fri, 05 May 2017 13:16:21 GMT

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.

Re: help in proc sql statement

Kurt_Bremser — Fri, 05 May 2017 13:18:59 GMT

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.

Re: help in proc sql statement

alexdsa310 — Fri, 05 May 2017 13:24:55 GMT

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;

Re: help in proc sql statement

Kurt_Bremser — Fri, 05 May 2017 13:31:45 GMT

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.

Re: help in proc sql statement

alexdsa310 — Fri, 05 May 2017 13:33:34 GMT

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.

Re: help in proc sql statement

Kurt_Bremser — Fri, 05 May 2017 13:38:09 GMT

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.

Re: help in proc sql statement

RW9 — Fri, 05 May 2017 13:40:28 GMT

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.

Re: help in proc sql statement

Kurt_Bremser — Fri, 05 May 2017 13:42:43 GMT

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