topic Re: Assessing column in table during DATA step merge in SAS Programming

Assessing column in table during DATA step merge

tomcmacdonald — Mon, 17 Sep 2018 17:16:56 GMT

Let's say I have two tables that are being merged and I need to access the value of a column in one to determine the value of a new column in the resulting join. For instance:

data foo;
  input foo $;
  datalines;
a
b
c
d
;
run;

data bar;
  input foo $;
  datalines;
a
b
c
d
;
run;

data merged;
  merge foo(in=a) bar(in=b);
  by foo;
  if a.foo = 'a' then myvar = 1;
run;

I'm getting this error:

ERROR: DATA STEP Component Object failure.  Aborted during the COMPILATION phase.
ERROR 557-185: Variable a is not an object.

How can I check the value of table foo column a within the DATA step?

Re: Assessing column in table during DATA step merge

tomrvincent — Mon, 17 Sep 2018 17:23:27 GMT

if foo = 'a' then myvar = 1; works

Re: Assessing column in table during DATA step merge

Reeza — Mon, 17 Sep 2018 17:23:34 GMT

When variables are merged with the same name, the one on the right overwrites the first one, so you need to rename them so they're different variables and then do a comparison.

Or you may be able to get what you want here with:

if a and foo='a' then ....

Re: Assessing column in table during DATA step merge

SuryaKiran — Mon, 17 Sep 2018 17:26:08 GMT

IN= is a dataset option that will give Boolean values (Always 0 or 1). It is not an alias name for the dataset like in sql alias reference.

Creates a Boolean variable that indicates whether the data set contributed data to the current observation.

http://support.sas.com/documentation/cdl/en/ledsoptsref/69751/HTML/default/viewer.htm#n1p1o2dsuc465nn198ovwdrj9mvy.htm

Re: Assessing column in table during DATA step merge

tomcmacdonald — Mon, 17 Sep 2018 17:26:44 GMT

What if the table and the variable have the same name? Let's say there's a table a and a column a in that table. Can you do something like this:

if a and a = 0 then myvar = 1;

Re: Assessing column in table during DATA step merge

Reeza — Mon, 17 Sep 2018 17:28:22 GMT

That would be silly programming to set the IN= variable to one that already exists....not that people don't do that I suppose. I would consider that user error. I get wanting to understand the theory though.

@tomcmacdonald wrote:

What if the table and the variable have the same name? Let's say there's a table a and a column a in that table. Can you do something like this:
if a and a = 0 then myvar = 1;

Re: Assessing column in table during DATA step merge

tomcmacdonald — Mon, 17 Sep 2018 17:29:39 GMT

I'm just thinking from a SQL perspective where you can do something like this:

case
  when a.a = 0 then 1
end as myvar

Re: Assessing column in table during DATA step merge

Reeza — Mon, 17 Sep 2018 17:35:03 GMT

In a data step you don't reference the table name though. You can find a record using the IN but the individual access to the different variables with the same names isn't easily possible. You can probably access it somehow, but that's beyond my skills. Most like @Tomor @data_null__ , or @Ksharp may know the answer.

Re: Assessing column in table during DATA step merge

Tom — Tue, 18 Sep 2018 03:25:05 GMT

@tomcmacdonald wrote:

I'm just thinking from a SQL perspective where you can do something like this:
case
  when a.a = 0 then 1
end as myvar

Doesn't really make much sense.

There is just one set of variables in a data step. No matter how many inputs you are reading or output you are writing.

If you need to reference both variables then one will need to have its name changed.

 merge a(rename=(a=a_from_a)) b;