adding a variable from one data set to another

[NinaWestern]

Hello everyone,

I would appreciate your input.

I need to add a variable from one data set to another one. They are both sorted by the key variable. I used a proc sql syntax posted here in another thread, but keep receiving error messages.

DDini- main data set

DD2- the second data set, from which I need only one variable, named ib14_new.

Here is my syntax:

proc sql;

create table DDm

as select work.DDini.*, work.DD2.ib14_new

from work.DDini left join work.DD2

on work.DDini.crn= work.DD2.crn;

quit;

The error message is attached.

Any help resolving this is very appreciated.

[ballardw · Accepted solution]

Because of the several ways that SAS uses a dot in constructing various items in syntax you can't have more than one . (other than macro variables) to  reference data set and variable names. So you need to create an alias that means the library.dataset combination.

proc sql;
   create table DDm
   as select A.*, B.ib14_new
   from work.DDini as A left join work.DD2 as B
   on A.crn= B.crn;
quit;

I explicitly use "as A" to set the alias but simple "work.DDini A" would work.

Of course the default library for data sets with a single level name means you can probably drop the "work." everywhere

BTW it is best on this forum to paste text such as LOG entries or code into a text box opened on the forum with the </> icon to preserve formatting.

Many won't open DOCX, or several other file formats from unknown users either by choice or organization policy.

[NinaWestern]

@ballardw, thank you! It did work!