topic Re: tagging in SAS Programming

tagging

milts — Fri, 05 Sep 2008 07:08:29 GMT

Hello,

let's say i have tables a and b.

a has var1 var2 and b has var3 var4

then i need to create a table that adds a column to table a named tag which depends on the first 5 chars of var1. so when substr(var1,1,5) is equal to a certain value of var3 then tag = var4.

here's the code:

proc sql;
select * from a left join b
on substr(var1,1,5) = var3;
quit;

assuming that there are only two values for var4. how come when i create a table that separates the two values of var3, their total count is different to the whole?

i feel i wasn't able to explain it well but if you understood what i'm trying to talk about your help is much appreciated. is there wrong with my code? or logic? or other thing? thanks a lot! 😃

Re: tagging

LinusH — Fri, 05 Sep 2008 07:40:25 GMT

One have to be careful when joining tables. In most cases you wish to join on key columns, that is the on/where-clause should contain unique values at least in on of the tables being joined, otherwise there might unpredictable results. Has var3 unique vales? Please attach some example data (of both input and expected output).

Regards,
Linus

Re: tagging

RichardDeVen — Wed, 10 Sep 2008 15:30:57 GMT

Your question is one of performing a lookup. In SQL creating a table with a resolved lookup generally involves the use of a LEFT JOIN.

data one;
length var1 var2 $8;
input var1 var2;
datalines;
abcdef qwerty
bcdefg tyuiop
cdefgh zxcvb
run;

data two;
length var3 var4 $8;
input var3 var4;
datalines;
abcde Richard
xyzab Linus
run;

proc sql;
create table one_tagged as
select
one.*
, two.var4 as tag
from
one
left join
two
on
substr(one.var1,1,5) = two.var3
;
quit;

Note that these types of joins are often utilized as a VIEW stored in a permanent library (create view instead of create table). That way the join is always upto date.

Furthermore, this type of lookup (left join) can also be accomplished using custom formats. A common use of formats is to map a range of values to some other value (a lookup)

Richard A. DeVenezia
http://www.devenezia.com