How to iteratively process datasets for one procedure

[PaigeMiller]

---

@krisraa wrote:

IF i use Do loop then i'm getting the same error.

---

Hello, @krisraa

We're getting nowhere. We keep asking for your SASLOG with specific options turned on, and either you don't show us, or you haven't turned on all the options. We'd like to help, but you have to do your part.

[krisraa]

Hi

You were suggesting there are other ways to achieve the same i'm trying to achieve. if you could hint something it will be helpful.

[Reeza]

What SAS version are you using?

[Reeza]

Does the following work? You'll need to update the library reference most likely but everything else should require NO CHANGES. 

If it does't post the log. If it does, I would change it to your code step by step until you run into the error again. 

[Reeza]

*create a bunch of datasets to mimic your procedure;

%macro loop;
    %do i=1 %to 5;

        data class&i.;
            set sashelp.class;
        run;

    %end;
%mend;

%loop;
*create fake location to store data;
libname out '/folders/myfolders/';
*macro to sort;

%macro sort();
    proc sql noprint;
        select count(*) into :dscnt from sashelp.vtable where libname='WORK' AND 
            MEMNAME LIKE 'CLASS%';
    quit;

    %put &dscnt;

    %do i=1 %to &dscnt;

        data _null_;
            set sashelp.vtable (OBS=&I FIRSTOBS=&I);
            where libname='WORK' and memname like 'CLASS%';
            call symputx("inlibref", strip(libname));
            call symputx("inmember", strip(memname));
        run;

        proc sort data=&inlibref..&inmember. out=out.&inmember (drop=weight height);
            by name;
        run;

    %end;
%mend;

%sort;

[krisraa]

Thanks a lot Reeza.

Finally identified what made that line and column error.

i was calling the spds macro like "spds()" that is with paranthesis.

Once i removed the paranthesis it worked perfect.

below is the code. I'm wondering if you could explain what does that paranthesis mean will be helpful to me as well as the community.

%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') firstobs=&i obs=&i);
			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]

And you've modified the code I'm sure so you should include your latest version. It's possible you introduced another error while fixing something else.

[PaigeMiller]

---

@krisraa wrote:

There is only 23 datasets in the library, but it also contains other files like .txt, .zip, .sas files as well.

Is that a problem ?

if yes how do i explicitly say only to sort sas7bdat files

---

I think the problem is that there could be SAS Views or other types of SAS tables (I'm not sure if there are any other types, but I mention it for completeness).

Anyway

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

I would also discourage writing code where you explicitly keep track of the counter in the looping as your are doing, while I don't know if this is the cause of your error, there are easier and cleaner ways to code a loop.

%do i = 1 %to &dscnt;
  .... code within the loop ...
%end;

[PaigeMiller]

Can we see the log with 

options mprint;

turned on, as suggested earlier by @Reeza?

[krisraa]

code

%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 %while (&i <= &dscnt);
		
		data _null_;
			set sashelp.vtable (where=(libname='SERETIDE') firstobs=&i. obs=&i. );
			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;

		
options mprint;
%spDS();

 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.
 58         
 59         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 SYMBOLGEN:  Macro variable GRAPHTERM resolves to GOPTIONS NOACCESSIBLE;

[krisraa]

for some reason the macro variable 'i' referred to 'obs=' resolves to 24 after the last iteration.

[Reeza]

Have you tried the %DO loop suggested? I suspect that will fix the problem. 

It has something to do with execution times I suspect, so this would be my first attempt at a solution.

[PaigeMiller]

It has been specifically requested, twice, that you turn on the MPRINT option.

[krisraa]

apologies you mean only mprint?

[PaigeMiller]

Quoting @Reeza in reply #10:

options mprint symbolgen mlogic;

Those are the options you need to show the full log.