topic Re: Identify Table Name in SAS Procedures

Identify Table Name

OS2Rules — Tue, 15 Dec 2009 14:03:47 GMT

Hi All:

This is more programming than procedure oriented ...

I build a list of table names using a PROC SQL using the INTO.

I then use this macro variable to read a set of tables into one:

DATA ACTIVITY:
SET &members;

etc.

It could process up to 127 "members" into the single table.

My question is: can I add a variable to ACTIVITY that identifies which table in the list of &members each observation came from?

Thanks in advance.

Re: Identify Table Name

sbb — Tue, 15 Dec 2009 14:28:04 GMT

Add a RETAIN statement that resolves a SAS variable in your DATA step. In the statement, declare the macro variable in double-quotes as the variable value. Add an ATTRIB statement to document the variable.

Scott Barry
SBBWorks, Inc.

Re: Identify Table Name

OS2Rules — Tue, 15 Dec 2009 14:31:32 GMT

Scott:

That won't work - the &members macro variable contains up to 127 table names that are basically being concatenated.

I need to know which one contributed each observation.

Re: Identify Table Name

sbb — Tue, 15 Dec 2009 14:38:02 GMT

Okay, then use the IN= parameter on the SET statement, and assign a concatenated string of subfield values (how about one char per input file), and use the "1" or "0" indicator from the IN= to detect when one of your input files (to the SET) is contributing an observation.

Scott Barry
SBBWorks, Inc.

Re: Identify Table Name

Flip — Tue, 15 Dec 2009 14:44:22 GMT

What if you create your macro variable with (in = ) options built in. You may still need create a string to keep those values as in = vars are dropped at the end of the data step.

select name || '(in = ' || name ||')' separated by ' ' into :

Re: Identify Table Name

data_null__ — Tue, 15 Dec 2009 15:29:16 GMT

There is a SET statement option for this in V9.2 if you have less than 9.1.3 like me you can do something like....

[pre]
data one two three four five;
x = 1;
run;

proc sql;
select cats(memname,'(in=__',memname,')') into :codeGen separated by ' '
from dictionary.members where libname eq 'WORK';
quit;
run;
%put NOTE: CODEGEN=&codegen;
data all;
set &codegen;
array _

__:;
length origin $32;
origin = substr(vname(_[whichn(1,of _

)]),3);
run;
proc print;
run;
[/pre]

Re: Identify Table Name

Peter_C — Wed, 16 Dec 2009 12:59:34 GMT

as pointed out by data _null_, there is an option in SAS92. The topic is covered already at http://support.sas.com/forums/thread.jspa?threadID=8013 , pointing to Usage note on option INDSNAME= {VARname} see http://support.sas.com/kb/34/513.html . I managed this back in 2001, before INdsname= was available, with the techniques referred by earlier posters. My solution can be found on SAS-L at
http://listserv.uga.edu/cgi-bin/wa?A2=ind0111A&L=sas-l&P=22975

Message was edited by: Peter.C to refer to sas-L, 2001 fixing broken link 21/2/10. Message was edited by: Peter.C