BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
alexdsa310
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

9 REPLIES 9
LinusH
Tourmaline | Level 20

And why is that?

Data never sleeps
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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