CODEWITHSUNDEEP
loopsmacrossasdataset
Neal801
Neal801

Reputation: 307

SAS - How to modify several datasets using a macro?

I'm trying to modify a number of datasets (their names follow a certain order, like data_AXPM061203900_20120104 , data_AXPM061203900_20120105, data_AXPA061204100_20120103, data_AXPA061204100_20120104) under work library. For example, I want to delete variable "price=0" in all datasets.

I am using the following to create a table to identify the datasets:

    proc sql ;
      create table data.mytables as
      select *
      from dictionary.tables
      where libname = 'WORK' 
      order by memname ;
      quit ;

For the next step, I'm trying to use a macro:

%macro test;
  proc sql ;
    select count(memname) into: obs from data.mytables;

  %let obs=&obs.;
    select catx("_", "data", substr(memname, 6, 13), substr(memname,20,27))
    into :setname1-:setname&obs.
    from data.mytables;
quit;


%do i=1 %to &obs.;

data  &&setname&i
set  &&setname&i

if bid_price= '.' then delete;
%end;
%mend test;

However, it completely failed. Could anyone give me some suggestions? I'm really not good at macros. Errors include:

  1. 56: LINE and COLUMN cannot be determined.
  2. ERROR 56-185: SET is not allowed in the DATA statement when option DATASTMTCHK=COREKEYWORDS. Check for a missing semicolon in the DATA statement, or use DATASTMTCHK=NONE.

Upvotes: 1

Views: 439

Answers (1)

Quentin
Quentin

Reputation: 6378

You are missing semicolons on the DATA statement and SET statement, and should probably add a RUN statement. Suggest you try:

%do i=1 %to &obs.;

  data  &&setname&i ;
    set  &&setname&i ;    
    if bid_price= '.' then delete;
  run;

%end;

Note that the DELETE statement is deleting records, not deleting variables. The code above expects that bid_price is a character variable, and that you want to delete records when the value is '.'.

Upvotes: 1

Related Questions