BookmarkSubscribeRSS Feed
krisraa
Quartz | Level 8

How to process all datasets in a library iteratively.

 

Below is what I'm trying but getting error.

 

%macro spDS;
%local dsCnt;

proc sql ;
   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=1 obs=1 );
            call symput("inlibref",strip(libname));
            call symput("inmember",strip(memname));
            call symput("outlibref",strip(strans));
            put _all_;
        run;
        
        proc sort data=&inlibref..&inmember. out= &outlibref..&inmember.
                    (drop = subjectstatus form formentrydate subjectvisitformid);
                     by subjectid;
                run;        
     %let i=%eval(&i.+1);
    %end;
    
%mend;
44 REPLIES 44
PaigeMiller
Diamond | Level 26

Is it simply fixing one line of code? Like this:

 

set sashelp.vtable (where=(libname='SERETIDE') firstobs=&i obs=&i );

If that's not it then I think to get any meaningful answer, you'd need to tell us what error you are getting. Show us!!!! Gives us details. Lots and lots of details.

--
Paige Miller
Reeza
Super User

@krisraa wrote:

 

Below is what I'm trying but getting error.

 


 

There's no error shown in your post. 

PaigeMiller
Diamond | Level 26

It says right there in the SAS log that STRANS is uninitialized ... the variable doesn't exist. You have to fix that problem.

--
Paige Miller
Reeza
Super User

STEP 1. READ YOUR LOG. 

 

It has an error:

 

 NOTE: Line generated by the macro variable "OUTLIBREF". 

56          .             _             22             76
 ERROR 22-322: Expecting a name. ERROR 76-322: Syntax error, statement will be ignored.

 

So somethings wrong with the OUTLIBREF assignment. 

STEP 2. Check the OUTLIBREF assignment

 

call symput("outlibref",strip(strans));

 

STEP 3. Check if this is working, or why not. Hint, STRANS isn't a variable on the SASHELP.VTABLE. 

 

In future posts, please include the log if you have an error. This isn't the first time we've had to ask for this either....

krisraa
Quartz | Level 8

Thanks will ensure going fwd.

 

Now I have removed the outlibref from the code and hardcoded the "strans" reference in the Proc Sort. procedure.

 

Now getting error as attached.

Reeza
Super User

Post the errors directly into the forum.

 

Add the macro debugging options and rerun your macro so we can see the actual code.

And include only the section with the errors because I don't want to scan through 10 pages of your log. 

 

Some resources on macro debugging:

http://www.lexjansen.com/nesug/nesug10/ff/ff10.pdf

http://www.lexjansen.com/scsug/2013/Strategies-and-Techniques-for-Debugging-SAS-Program-Errors-and-W...

krisraa
Quartz | Level 8

The iterations are complete and datasets were sorted but after the last iteration the below error is occuring.

 

 MLOGIC(SPDS):  %LET (variable name is I)
 MLOGIC(SPDS):  %DO %WHILE(&i. <= &dsCnt.) condition is TRUE; loop will iterate again.
 
 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
       
 
 
 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.00 seconds
       
 
 MLOGIC(SPDS):  %LET (variable name is I)
 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.
 87         
 88         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 100        
Reeza
Super User
options mprint symbolgen mlogic;


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

krisraa
Quartz | Level 8
 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         
 60         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 SYMBOLGEN:  Macro variable GRAPHTERM resolves to GOPTIONS NOACCESSIBLE;
 72         
Reeza
Super User
 SYMBOLGEN:  Macro variable I resolves to 23
 SYMBOLGEN:  Macro variable I resolves to 24
 SYMBOLGEN:  Macro variable DSCNT resolves to       23

 

That doesn't make sense....check what the 23/24th entry in the data set is and why it may be causing an issue. 

krisraa
Quartz | Level 8

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

Reeza
Super User

Check the data set...and your loop conditions. 

What happens if you use a straight %do loop rather than do while?

 

krisraa
Quartz | Level 8

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

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