How to iteratively process datasets for one procedure

[krisraa]

 Yes I have attached the log previously with all options enabled.

Here it goes again.

 MLOGIC(SPDS):  %LET (variable name is I)
 SYMBOLGEN:  Macro variable I resolves to 21
 SYMBOLGEN:  Macro variable I resolves to 22
 SYMBOLGEN:  Macro variable DSCNT resolves to       23
 MLOGIC(SPDS):  %DO %WHILE(&i <= &dscnt) condition is TRUE; loop will iterate again.
 MPRINT(SPDS):   data _null_;
 SYMBOLGEN:  Macro variable I resolves to 22
 SYMBOLGEN:  Macro variable I resolves to 22
 MPRINT(SPDS):   set sashelp.vtable (where=(libname='SERETIDE') firstobs=22 obs=22 );
 MPRINT(SPDS):   call symput("inlibref",strip(libname));
 MPRINT(SPDS):   call symput("inmember",strip(memname));
 MPRINT(SPDS):   run;
 
 NOTE: There were 1 observations read from the data set SASHELP.VTABLE.
       WHERE libname='SERETIDE';
 NOTE: DATA statement used (Total process time):
       real time           0.05 seconds
       cpu time            0.06 seconds
       
 
 SYMBOLGEN:  Macro variable INLIBREF resolves to SERETIDE
 SYMBOLGEN:  Macro variable INMEMBER resolves to SV
 SYMBOLGEN:  Macro variable INMEMBER resolves to SV
 MPRINT(SPDS):   proc sort data=SERETIDE.SV out= strans.SV (drop = subjectstatus form formentrydate 
 subjectvisitformid);
 MPRINT(SPDS):   by subjectid;
 MPRINT(SPDS):   run;
 
 NOTE: There were 120 observations read from the data set SERETIDE.SV.
 NOTE: The data set STRANS.SV has 120 observations and 10 variables.
 NOTE: PROCEDURE SORT used (Total process time):
       real time           0.04 seconds
       cpu time            0.00 seconds
       
 
 MLOGIC(SPDS):  %LET (variable name is I)
 SYMBOLGEN:  Macro variable I resolves to 22
 SYMBOLGEN:  Macro variable I resolves to 23
 SYMBOLGEN:  Macro variable DSCNT resolves to       23
 MLOGIC(SPDS):  %DO %WHILE(&i <= &dscnt) condition is TRUE; loop will iterate again.
 MPRINT(SPDS):   data _null_;
 SYMBOLGEN:  Macro variable I resolves to 23
 SYMBOLGEN:  Macro variable I resolves to 23
 MPRINT(SPDS):   set sashelp.vtable (where=(libname='SERETIDE') firstobs=23 obs=23 );
 MPRINT(SPDS):   call symput("inlibref",strip(libname));
 MPRINT(SPDS):   call symput("inmember",strip(memname));
 MPRINT(SPDS):   run;
 
 NOTE: There were 1 observations read from the data set SASHELP.VTABLE.
       WHERE libname='SERETIDE';
 NOTE: DATA statement used (Total process time):
       real time           0.05 seconds
       cpu time            0.03 seconds
       
 
 SYMBOLGEN:  Macro variable INLIBREF resolves to SERETIDE
 SYMBOLGEN:  Macro variable INMEMBER resolves to VS
 SYMBOLGEN:  Macro variable INMEMBER resolves to VS
 MPRINT(SPDS):   proc sort data=SERETIDE.VS out= strans.VS (drop = subjectstatus form formentrydate 
 subjectvisitformid);
 MPRINT(SPDS):   by subjectid;
 MPRINT(SPDS):   run;
 
 NOTE: There were 660 observations read from the data set SERETIDE.VS.
 NOTE: The data set STRANS.VS has 660 observations and 14 variables.
 NOTE: PROCEDURE SORT used (Total process time):
       real time           0.03 seconds
       cpu time            0.01 seconds
       
 
 MLOGIC(SPDS):  %LET (variable name is I)
 SYMBOLGEN:  Macro variable I resolves to 23
 SYMBOLGEN:  Macro variable I resolves to 24
 SYMBOLGEN:  Macro variable DSCNT resolves to       23
 180: LINE and COLUMN cannot be determined.
 NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where 
       the error has occurred.
 ERROR 180-322: Statement is not valid or it is used out of proper order.
 MLOGIC(SPDS):  %DO %WHILE() condition is FALSE; loop will not iterate again.
 MLOGIC(SPDS):  Ending execution.
 86         
 87         
 88         
 89         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 SYMBOLGEN:  Macro variable GRAPHTERM resolves to GOPTIONS NOACCESSIBLE;

[Reeza]

What happened when you switched from DO WHILE to a DO loop?

Still waiting on that output.

[krisraa]

options mprint mlogic symbolgen;
%macro spDS;
%local dsCnt inlibref inmember i;

proc sql noprint  ;
   select count(*)into :dsCnt from sashelp.vtable where libname='SERETIDE' ;
quit;

	%let i=1;

	%do i = &i %to &dscnt;
		
		data _null_;
			set sashelp.vtable (where=(libname='SERETIDE'));
			call symput("inlibref",strip(libname));
			call symput("inmember",strip(memname));
		run;
		
			proc sort data=&inlibref..&inmember. out= strans.&inmember.
					 (drop = subjectstatus form formentrydate subjectvisitformid);
					  by subjectid;
			run;		
/* 	 %let i=%eval(&i.+1); */

	%end; 
	
%mend;
	

%spDS();

 MLOGIC(SPDS):  %DO loop index variable I is now 23; loop will iterate again.
 MPRINT(SPDS):   data _null_;
 MPRINT(SPDS):   set sashelp.vtable (where=(libname='SERETIDE'));
 MPRINT(SPDS):   call symput("inlibref",strip(libname));
 MPRINT(SPDS):   call symput("inmember",strip(memname));
 MPRINT(SPDS):   run;
 
 NOTE: There were 23 observations read from the data set SASHELP.VTABLE.
       WHERE libname='SERETIDE';
 NOTE: DATA statement used (Total process time):
       real time           0.53 seconds
       cpu time            0.54 seconds
       
 
 SYMBOLGEN:  Macro variable INLIBREF resolves to SERETIDE
 SYMBOLGEN:  Macro variable INMEMBER resolves to VS
 SYMBOLGEN:  Macro variable INMEMBER resolves to VS
 MPRINT(SPDS):   proc sort data=SERETIDE.VS out= strans.VS (drop = subjectstatus form formentrydate 
 subjectvisitformid);
 MPRINT(SPDS):   by subjectid;
 MPRINT(SPDS):   run;
 
 NOTE: There were 660 observations read from the data set SERETIDE.VS.
 NOTE: The data set STRANS.VS has 660 observations and 14 variables.
 NOTE: PROCEDURE SORT used (Total process time):
       real time           0.09 seconds
       cpu time            0.01 seconds
       
 
 180: LINE and COLUMN cannot be determined.
 NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where 
       the error has occurred.
 ERROR 180-322: Statement is not valid or it is used out of proper order.
 MLOGIC(SPDS):  %DO loop index variable I is now 24; loop will not iterate again.
 MLOGIC(SPDS):  Ending execution.
 86         
 87         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 SYMBOLGEN:  Macro variable GRAPHTERM resolves to GOPTIONS NOACCESSIBLE;
 99         

[Reeza]

I feel like this should work or something is happening that we're not seeing. 

I would restart SAS and check if the same behaviour occurs. If it does, I'd probably contact SAS Support. I don't see anything in the code that should cause those issues, though it's easy to miss things. 

[krisraa]

I tried retyping the code completely in as new program and also restarted the sas session but still no luck.

[Reeza]

Do you get the same issue if you only run it to 5 iterations? Hard code the 5 in. 

[krisraa]

options mprint mlogic symbolgen;
%macro spDS;
%local dsCnt inlibref inmember i;

proc sql noprint ;
   select count(*) into :dsCnt from sashelp.vtable where libname='SERETIDE' ;
quit;

	%let i=1;

	%do i = &i %to 5;
		
		data _null_;
			set sashelp.vtable (where=(libname='SERETIDE'));
			call symput("inlibref",strip(libname));
			call symput("inmember",strip(memname));
		run;
		
			proc sort data=&inlibref..&inmember. out = strans..&inmember
					 (drop = subjectstatus form formentrydate subjectvisitformid);
					  by subjectid;
			run;		


	%end; 
	
%mend;
			

%spDS();

Revised code with 5 iterations, got a whole different error.

1. The inmember resolves to the last dataset during the first iteration itself as VS (so only this dataset is sorted and saved in strans library

2. Next in the out= option the macro call of inmember causing the issue.

Attached the bit long log to review from begining of the loop.

[Reeza]

 You also dropped the WHERE condition. 

[krisraa]

Yep  included the firstobs and obs now getting the same 180 error.

i tried for 1 dataset to 5 dataset.

Also, tried for only one dataset still getting the same error.

[error_prone]

A fascinating problem. I assume that nobody has write-access to be library while executing the macro. Instead of reading from sashelp.vtable in the loop create a dataset containg the extract of vtable and use that clone inside the loop.

[krisraa]

not completely getting your point if you could give a sample code will be helpful

[error_prone]

untested, of course

options mprint mlogic symbolgen;
%macro spDS;
	%local dsCnt inlibref inmember i;

	proc sql noprint ;
	   create table work.selection as
			select libname, memname
				from sashelp.vtable
					where libname = 'SERETIDE'
	   ;
	   select count(*) into :dsCnt from sashelp.vtable where libname='SERETIDE' ;
	quit;

	%do i = 1 %to 5;
		
		data _null_;
			set work.Selection;
			call symput("inlibref", strip(libname));
			call symput("inmember", strip(memname));
		run;
		
			proc sort data=&inlibref..&inmember. out = strans..&inmember
					 (drop = subjectstatus form formentrydate subjectvisitformid);
					  by subjectid;
			run;		


	%end; 
	
%mend;
			

%spDS();

[Reeza]

Interesting. 

It seems to want consistency, which makes sense.

If you declare the array with the parenthesis and then call with parenthesis it's fine. 
If you create with parenthesis and call without it's fine.

If you create without parenthesis and cal with parenthesis, it creates an error. 

The parenthesis are usually for when you have a macro with parameters, but you aren't using that style so they're not required.

[error_prone]

@Reezanot sure if i fully understood your post. Do you mean that the parenthesis in the macro-call are causing the chaos?

[Reeza]

@error_prone Yes, that appears to be the issue. The macro is not defined with parenthesis and when called with parenthesis it errors out on the last iteration. I replicated this with the code I posted earlier (page 2 bottom depending on how you view this) using SAS 9.4 TS1M3.