topic Re: append a column to a dataset from another table in SAS Programming

append a column to a dataset from another table

thryce85 — Sat, 15 Jan 2022 18:52:02 GMT

Practing SAS sql and curious on how to directly append another column from a seperate data set into a different data set without having a unique key to join on. Say I table A with customers and address in one table and Table B has

customers and account number . Customers is not unique (lots of Joe Smith's in the world) but I know they were added to the system in exactly the same order and records are in a one-to-one manner. How do I aggregate the two into a master table? Sure its simple so sorry for being a bother. Thanks !!

what I tried

------------------------------------------

data d1
set sashelp.cars;
run;

proc sql;
/*adding column */
alter table d1
add make char;

/*trying to insert make values back into the table */
update d1 set make =
(select make from sashelp.cars);

quit;

Re: append a column to a dataset from another table

Reeza — Sat, 15 Jan 2022 18:57:19 GMT

I don't know if there's a SQL construct to do that, but there is in data step - MERGE without a BY statement.

data want;
merge d1 cars(keep=make);
run;

Re: append a column to a dataset from another table

thryce85 — Sat, 15 Jan 2022 19:05:22 GMT

awesome thanks for the info

Re: append a column to a dataset from another table

Tom — Sat, 15 Jan 2022 22:52:15 GMT

SQL is a relational algebra. So you need some RELATION to have it operate.

It has no way to refer to an observation by POSITION in the dataset, so there is no way in a pure SQL dialect you could combine data without some values to match on.

But a SAS data step will process the data from a dataset sequentially. You can just set the two datasets together.

data want;
  set d1;
  set sashelp.cars(keep=make);
run;

PS Using separate steps to "add" a variable to a dataset and populate the values makes no sense from a performance point of view.