SAS Macro - number of records to go further

[Filipvdr]

I want to loop through a list of datasets, check if they have more then one record, if so, i want to do something.

My macro variable obscnt does not work.. any help?

%macro Test(source);
data _null_;
call symput('obscnt',0);
set SAP_SRC."&source"n end=lastobs;
if lastobs then do;
call symput('obscnt',_n_);
end;
run;
%put &source=&obscnt;

%if &obscnt > 0 %then %do;

%end;
%mend;

data _null_;
set all_columns;
call execute('%Test(source='||strip(dataset)||')');
run;

[RW9]

Its really not clear from your post what you are trying to do.  The macro seems to call itself if something occurs.  Please post example test data in the form of a datastep and show what the output should be.  

You can simply query the sashelp metadata and use those to trigger macro calls with something like:

data _null_;
  set sashelp.vtable (where=(libname="YOURLIB" and nobs > 0));
  if _n_=1 then call execute('%...');
run;

[Filipvdr]

Hello,

In my table all_columns i have a list of columns, which exists as a table in SAP.

For every column, i call my macro Test. Here i want to check if the table in SAP has records.

If so, i want to create a format.

The only part i'm struggling with, is the part where i have to check if the table in SAP has records.

[RW9]

What is SAP?

If you have a libname to it then there will be metadata in sashelp.  

If you just want a format created why not proc freq all variables in each dataset, then use that as the basis for your format?

[Shmuel]

Please note:

1) In oreder to use LASTOBS as you used, you need to define it first:

%let obscnt = 0;
data _null_; 
   set  <your input> end=lastobs;
   if lastobs then call symput('obscnt',strip(_N_));
run;

2) Alternatively you can do - without reading your input sas dataset:

data _null_;
    id = open("<your input>");
    if id then do;
        nobs = attrn(id, 'nobs');
        call symput('obscnt', strip(nobs));
        id=close(id);
    end;
    else call symput('obscnt','0');
run;

[PSNn]

Be carefull... For SAP tables SAS do not know anything about number of records.  Stay comlpletely away from things like SASHELP.TABLES... I assume you know how many tables a SAP installation have (?)  Calling  SASHELP.TABLES may run for a very long time without giving you "anything". I think you are on your way to the best solution.   I do not immediately see what is wrong With Your code, but if you mean whart you say:. do not read to the end of the tables to figure out if it's time to do something.  More like

if _n_ = 2 then do;

  Call symputx('obscnt', '2');

  STOP;

end;

Will save a lot of time..  As I understand you are not interested in the number of rows, You just what to know that there is at least 2 rows.

[PSNn]

Ok.... Use this for setting the macro var to 0

if _n_ = 1 then  call symput('obscnt',0);

SAS WILL execute this one again  before terminating the step, hence resetting it to 0.

But still, do not read all the records in the SAP table just to figure at that there are "some" records...

[Filipvdr]

Already big thanks for your replies.

I know I can stop after 2 records, but now for testing i changed it to the last row.

Indeed, SASHELP tables in combination with SAP is impossible. Also, i don't want to create formats of every SAP table. Only those I want.

My code:

%macro Test(source);
data _null_;
set SAP_SRC."&source"n end=lastobs;
if _n_ = 1 then call symputx('obscnt',0);
if lastobs then Call symputx('obscnt', _N_);
run;
%put &source=&obscnt;
%if &obscnt > 0 %then %do;
%end;
%mend;

data _null_;
set all_columns;
call execute('%Test(source='||strip(dataset)||')');
run;

This is the output in my log: as you can see... it's always 50, but is because my last table has 50 records.. all the rest have more 

For example: NOTE: There were 1292 observations read from the data set SAP_SRC.'/BIC/TZMAGBEST'n.

So.. something is not working correctly with the macro variable i think? 

/BIC/TVAUFNR=50
/BIC/TZBEHVRSC=50
/BIC/TZBELEVFEQ=50
/BIC/TZFINTYPE=50
/BIC/TZMAGBEST=50
/BIC/TZMATPLANT=50
/BIC/TZMATPROF=50
/BIC/TZMKPFUSN=50
/BIC/TZMSATTYP=50
/BIC/TZMSBUKRS=50
/BIC/TZMSBUWD=50
/BIC/TZMSBWART=50
/BIC/TZMSBWKEY=50
/BIC/TZMSBWTAR=50
/BIC/TZMSDEFSH=50
2 The SAS System 12:49 Thursday, October 19, 2017

/BIC/TZMSGRUNDS=50
/BIC/TZMSGSBER=50
/BIC/TZMSKOKRS=50
/BIC/TZMSKOSTL=50
/BIC/TZMSKSTRG=50
/BIC/TZMSKUNNR=50
/BIC/TZMSKZEAR=50
/BIC/TZMSLGORT=50
/BIC/TZMSLIFNR=50
/BIC/TZMSMATNR=50
/BIC/TZMSPRCTR=50
/BIC/TZMSRWEMPF=50
/BIC/TZMSSAKTO=50
/BIC/TZMSSHKZG=50
/BIC/TZMSUMWRK=50
/BIC/TZMSWERKS=50
/BIC/TZMSZBWAR=50
/BIC/TZONWW=50
/BIC/TZSTPLOPHP=50
/BIC/TZUIDMATPO=50
/BIC/TZVSTBEST=50

[PSNn]

You need to have the    IF _N:_ = 1 ,,,

BEFORE the SET stmt !     (important)  .. as you did have in the original post

Or you  could of course have a

%LET obscnt  = 0 ;

before the datastep.

[PSNn]

As you have set it up, it will not handle tables With 0 rows.

Moving the statement as indicated will fix that

[Filipvdr]

If i change it like this, i reset it every time to zero: i also putted a put statement if my obscount is bigger then 0.

But still my macrovariable does not has the right value i think... or not at runtime

Code

%macro Test(source);
data _null_;
call symputx('obscnt',0);
set SAP_SRC."&source"n end=lastobs;
if lastobs then Call symputx('obscnt', _N_);
run;
%put &source=&obscnt;
%if &obscnt > 0 %then %do;
%put &source;
%end;
%mend;

data _null_;
set all_columns;
call execute('%Test(source='||strip(dataset)||')');
/*if dataset = "/BIC/TVAUFNR" then call execute('%CreateFormat(source='||strip(dataset)||',formatname=F_'||strip(column)||',type='||strip(type)||');');
*/
run;

'/BIC/TZMSZBWAR'n. is a dataset with zero records... but my macovariable says 50

I think it says 50 because my last table has 50 records... : SAP_SRC.'/BIC/TZVSTBEST'n.

Log:

NOTE: There were 0 observations read from the data set SAP_SRC.'/BIC/TZMSZBWAR'n.

NOTE: There were 50 observations read from the data set SAP_SRC.'/BIC/TZVSTBEST'n.

/BIC/TVAUFNR=50
/BIC/TVAUFNR
/BIC/TZBEHVRSC=50
/BIC/TZBEHVRSC
/BIC/TZBELEVFEQ=50
/BIC/TZBELEVFEQ
/BIC/TZFINTYPE=50
/BIC/TZFINTYPE
/BIC/TZMAGBEST=50
/BIC/TZMAGBEST
/BIC/TZMATPLANT=50
/BIC/TZMATPLANT
/BIC/TZMATPROF=50
/BIC/TZMATPROF
2 The SAS System 12:49 Thursday, October 19, 2017

/BIC/TZMKPFUSN=50
/BIC/TZMKPFUSN
/BIC/TZMSATTYP=50
/BIC/TZMSATTYP
/BIC/TZMSBUKRS=50
/BIC/TZMSBUKRS
/BIC/TZMSBUWD=50
/BIC/TZMSBUWD
/BIC/TZMSBWART=50
/BIC/TZMSBWART
/BIC/TZMSBWKEY=50
/BIC/TZMSBWKEY
/BIC/TZMSBWTAR=50
/BIC/TZMSBWTAR
/BIC/TZMSDEFSH=50
/BIC/TZMSDEFSH
/BIC/TZMSGRUNDS=50
/BIC/TZMSGRUNDS
/BIC/TZMSGSBER=50
/BIC/TZMSGSBER
/BIC/TZMSKOKRS=50
/BIC/TZMSKOKRS
/BIC/TZMSKOSTL=50
/BIC/TZMSKOSTL
/BIC/TZMSKSTRG=50
/BIC/TZMSKSTRG
/BIC/TZMSKUNNR=50
/BIC/TZMSKUNNR
/BIC/TZMSKZEAR=50
/BIC/TZMSKZEAR
/BIC/TZMSLGORT=50
/BIC/TZMSLGORT
/BIC/TZMSLIFNR=50
/BIC/TZMSLIFNR
/BIC/TZMSMATNR=50
/BIC/TZMSMATNR
/BIC/TZMSPRCTR=50
/BIC/TZMSPRCTR
/BIC/TZMSRWEMPF=50
/BIC/TZMSRWEMPF
/BIC/TZMSSAKTO=50
/BIC/TZMSSAKTO
/BIC/TZMSSHKZG=50
/BIC/TZMSSHKZG
/BIC/TZMSUMWRK=50
/BIC/TZMSUMWRK
/BIC/TZMSWERKS=50
/BIC/TZMSWERKS
/BIC/TZMSZBWAR=50
/BIC/TZMSZBWAR
/BIC/TZONWW=50
/BIC/TZONWW
/BIC/TZSTPLOPHP=50
/BIC/TZSTPLOPHP
/BIC/TZUIDMATPO=50
/BIC/TZUIDMATPO
/BIC/TZVSTBEST=50
/BIC/TZVSTBEST

[PSNn · Accepted solution]

It also has something to do withe the way Call execute Works.. I think.

I do not see why now... But this Works for me:

  %macro Test(source);
  data _null_;
  if _n_ = 1 then call symputx('obscnt',0);
  set SAP_prd."&source"n end=lastobs;
  if lastobs then Call symputx('obscnt', _N_);
  run;
  %put &source=&obscnt;
  %if &obscnt > 0 %then %do;
  %end;
  %mend;

data all_columns  ;
  dataset =    '/BI0/TPROFIT_CTR'    ; output;
  dataset =    '/BI0/TMATERIAL'   ; output;
run;

Filename test temp;

 data _null_;
   file test;
  set all_columns;

 Kode = '%Test(source='||strip(dataset)||')';
 put kode;
 run;

%INC test;

Try it !

(note that I changed the libname to make it fit in my Place)

[Filipvdr]

Thanks PSNn, now it works... I also don't really understand the call execute and the initialization of the macrovariables and how it works in runtime, but I knew it had to be something in that direction 🙂