Get all macro variable values

Reply
Contributor
Posts: 72

Get all macro variable values

Suppose I write :

%let a = abcd;

%let a = anant;

%let a = sharma;


And then I write

%put &a;

I get output as

sharma

But I want all the outputs of macro variable &a without assigning Anant and Sharma to different macro variable.

Can anybody help me in this?

Is that a chance that all these macro variable values stored somewhere in a session like dictionary.views or something

Super Contributor
Posts: 308

Re: Get all macro variable values

Hello,

it seems you want to concatenate the macro variable with itself.

%let a = abcd;

%let a = %sysfunc(cat(&a,* ,anant));

%let a = %sysfunc(cat(&a,* ,sharma));

%put &a;

Contributor
Posts: 72

Re: Get all macro variable values

Thanks for the reply but that won't solve my purpose

What I really want to do is, I have a job made in data integration studio.

When you observe the code, all the tables which is used is in the macro variable &syslast.

Now what I want to do is to get all the tables name of that job. But as the &syslast is keeps on changing the value I get the last table of that job.

Now somehow I want to get all the table names of that job.

Super User
Posts: 5,424

Re: Get all macro variable values

No need to use %sysfunc and cat() here.

Data never sleeps
Super Contributor
Posts: 340

Re: Get all macro variable values

Depending on what you are actually up to, this might be a way:

Data Macro_List;

  Length a $10.;
  a="abcd"; Output;
  a="anant"; Output;
  a="sharma"; Output;
Run;

Proc SQL NoPrint;
  Select A Into :A Separated By ', ' From Macro_List;
Quit;
%Put **&A.**;

Contributor
Posts: 72

Re: Get all macro variable values

Posted in reply to user24feb

Thanks for the reply but that won't solve my purpose

What I really want to do is, I have a job made in data integration studio.

When you observe the code, all the tables which is used is in the macro variable &syslast.

Now what I want to do is to get all the tables name of that job. But as the &syslast is keeps on changing the value I get the last table of that job.

Now somehow I want to get all the table names of that job.

Contributor
Posts: 65

Re: Get all macro variable values

Hi,

You will have to create a new macro variable other than syslast.

syslast is a default variable so it keeps changing.

If you want to access the datasets after certain steps,  you can define a variable locally/globally as below

%global lastdtst;

and theyou can add the below code after those steps.

%let lastdtst = %sysfunc(cat(&lastdtst , %str( )  ,&syslast ));

you can then call lastdtst to get the set of datasets that you would need later.

Contributor
Posts: 72

Re: Get all macro variable values

Posted in reply to DMoovendhan

Thanks.

But I want to keep the code as automatic generated no user written.

What I can do is do anything in the pre or post code.

Is there any way that behind jobs sas store all the table name used inside a job for a session.

Super Contributor
Posts: 340

Re: Get all macro variable values

Maybe you can 1. redirect the log, 2. write &sysfunc to the log in such a way that it can be recovered, 3. post-process the log.

I mean something like this:


Proc PrintTo Log="C:\<YOUR_PATH>\Log.Log";
Run;

%Macro dummy;
%Do i=1 %To 5;
  %Put ******;
  %Put Syslast=&Syslast. &i.; * --> and search for "Syslast=" later;
%End;
%Mend;
%dummy

Proc PrintTo;
Run;

Data Log_Check (Keep=Syslast);
  Infile "C:\Users\zwainma\Desktop\Test\Log.Log" Delimiter='09'x MISSOVER DSD Lrecl=32767 End=Eof;
  Informat LogLine $200.;
  Format LogLine $200.;
  Input Logline $;
  Retain Syslast;
  If Logline=:"Syslast=" Then Do;
    Syslast=LogLine;
    Output;
  End;
Run;


I know it's a bit complicated. But it's my best guess.

Contributor
Posts: 72

Re: Get all macro variable values

Posted in reply to user24feb

Well that's an approach. But what I just observed that &syslast macro variable is not reliable to get all tables used in a job.

Sometimes it stores and sometimes it doesn't.

Hence forth will have to look for a new approach.

Super User
Posts: 10,018

Re: Get all macro variable values

You can get all of &syslast , every time it will be changed ,no trace leave .

But you can do text analysis for your sas code and pick up all these dataset name present by &syslast .

Xia Keshan

Super User
Super User
Posts: 7,942

Re: Get all macro variable values

Why not be pro-active.  Instead of relying on system macro variables, your own macro variables, and lists of values, just insert into an audit trail what your code is doing at the time e.g.;

proc sql;

     create table LIST (DATASET_NAME char(200));

quit;

/* Gonna use TEMP dataset next */

proc sql;

     insert into LIST set DATASET_NAME="TEMP";

quit;

data temp;

     ...;

run;

/* Next using XYZ */

proc sql;

     insert into LIST set DATASET_NAME="XYZ";

quit;

data xyz;

     ...

...

Of course you could put the insert row after the dataset and then use syslast.  What this creates is a dataset with and audit trail which is what it looks like you are trying to achieve.

Contributor
Posts: 72

Re: Get all macro variable values

Hello rw9

What i want to do is to get record count of sources and target tables used in a job.

If I'm using an extract transformation then I want extract records and not the record count of that table.

I'm able to achieve all these things using a base sas macro which runs in my postcode but only problem is that I havE to specify my tables used as:

%let src_tgt = libref.source | libref.target

I want to get table names from the sas itself

Super User
Posts: 5,424

Re: Get all macro variable values

Hercules, what is the goal of this requirement, what do you wish to do with this information?

If wish to get detailed information on this level, you might want to take a look at ARM logging, which is activated by default in DIS.

Data never sleeps
Contributor
Posts: 72

Re: Get all macro variable values

I want to see how much data was in my source and how much data got inserted into the target.

Suppose I have n number of sources in a job, and I'm loading 2 tables with it I. E. Target are 2

Then I want to know the number of records from each source and number of records loaded into the target

Ask a Question
Discussion stats
  • 19 replies
  • 757 views
  • 1 like
  • 11 in conversation