Help using Base SAS procedures

Identify Table Name

Reply
Super Contributor
Posts: 358

Identify Table Name

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: Identify Table Name

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.
Super Contributor
Posts: 358

Re: Identify Table Name

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: Identify Table Name

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.
Super Contributor
Posts: 359

Re: Identify Table Name

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 :
Respected Advisor
Posts: 3,777

Re: Identify Table Name

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]
  • Valued Guide
    Posts: 2,174

    Re: Identify Table Name

    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
    Ask a Question
    Discussion stats
    • 6 replies
    • 153 views
    • 0 likes
    • 5 in conversation