Re: Wrong data step to flag data

You want to do a table lookup. Below 2 tested code options of how you can achieve this. A "Merge" as Art suggests would be another alternative - and if you Google you would also find examples using the data step Array statement or for a single key/value pair lookup the use of SAS formats.

/* SQL option */proc sql; create table want1 as select h.*, case(l.edit) when '0' then 1 else 0 end as flag from have h left join lookup l on h.proc1=l.proc1 and h.proc2=l.proc2 ;quit;

Your proposed solution won't work as an interleaving set statement reads only from one table in one iteration of a data step. Using the sample data your result table would contain 8 rows.

I believe it should be a left-join. Using an inner join would result in 2 rows only.

As Art mentioned: If using a join/merge make sure the (composite) key used for joining is unique in your look-up table.

When loading a SAS Hash de-duplication happens automatically if not using "multidata". Also: In a normal scenario a lookup table is normally rather small but the base table can be quite big. Loading the lookup table into a hash avoids the need for sorting (either explicit as preparation for a "merge" or implicit done by the SQL when joining the tables). Coding a hash is a bit more typing but gives you often better performance.