BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Filipvdr
Pyrite | Level 9

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PSNn
Obsidian | Level 7

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)

View solution in original post

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Pyrite | Level 9
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Garnet | Level 18

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Pyrite | Level 9

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
Obsidian | Level 7

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
Obsidian | Level 7

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

Moving the statement as indicated will fix that

Filipvdr
Pyrite | Level 9

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
Obsidian | Level 7

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
Pyrite | Level 9

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 🙂 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1289 views
  • 0 likes
  • 4 in conversation