@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.
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.
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.
*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;
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;
@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;
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;
for some reason the macro variable 'i' referred to 'obs=' resolves to 24 after the last iteration.
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.
It has been specifically requested, twice, that you turn on the MPRINT option.
apologies you mean only mprint?
Quoting @Reeza in reply #10:
options mprint symbolgen mlogic;
Those are the options you need to show the full log.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: