BookmarkSubscribeRSS Feed
PaigeMiller
Diamond | Level 26

@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.

--
Paige Miller
krisraa
Quartz | Level 8

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
Super User
What SAS version are you using?
Reeza
Super User

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
Super User
*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
Quartz | Level 8

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
Super User
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
Diamond | Level 26

@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;
--
Paige Miller
PaigeMiller
Diamond | Level 26

Can we see the log with 

 

options mprint;

turned on, as suggested earlier by @Reeza?

--
Paige Miller
krisraa
Quartz | Level 8

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
Quartz | Level 8

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

Reeza
Super User

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
Diamond | Level 26

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

--
Paige Miller
krisraa
Quartz | Level 8

apologies you mean only mprint?

PaigeMiller
Diamond | Level 26

Quoting @Reeza in reply #10:

 

options mprint symbolgen mlogic;


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

--
Paige Miller

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
  • 44 replies
  • 2098 views
  • 7 likes
  • 4 in conversation