Update SAS dataset based on results from a PROC SQL from another dataset

Question

New SAS user here: I have a dataset (table) created by a PROC SQL statement:

proc sql;
    create table work.dm_1 as
    select distinct a.subject, c.dsyn as DSYN_DS_1, d.dsyn as DSYN_DS_2
    from s_raw.dm_1 a
    left join work.edt_eg b
    on a.subject=b.subjid
    left join s_raw.ds_1 c
    on a.subject=c.subject
    left join s_raw.ds_2 d
    on a.subject=d.subject
    where c.DSYN='NO' and d.DSYN='NO';
quit;

Using the results from this table (work.dm_1), I want to modify another (existing) table (work.edt_ecg, created from a previous procedure) to select matching records (using subject in dm_1 and subjid in edt_eg) from the table in the proc sql above, and to update the table work.edt_ecg. I have tried the following:

proc sql;
   update table work.edt_eg as
   select * 
   from work.edt_eg where subjid in (select distinct subject from      work.dm_1); 
quit;

But its not working for me! Any ideas welcome

You might be making errors using left join s_raw.ds_1 c and then filtering on c.DSYN='NO': you loose your observation(1) from a when there is no matching c. You should write left join s_raw.ds_1 c on a.subject=c.subject and c.DSYN='NO'. The same accounts to your left join on d — Dirk Horsten

Dirk Horsten Dirk Horsten · Accepted Answer · 2015-08-05T10:38:28

You should either create a new table or view with the subset of the data you need

create view work.edt_eg_wanted as
select * 
from work.edt_eg 
where subjid in (select distinct subject from work.dm_1);

or delete all unwanted observations (1) from the existing table

delete * 
from work.edt_eg 
where subjid not in (select distinct subject from work.dm_1);

(As you did not supply sample data, this code is not tested)

(1) Observation is the word SAS uses for row in a table. This because SAS was originally written by statisticians.

Update SAS dataset based on results from a PROC SQL from another dataset

1 Answers