How to modify several dataset with macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

How to modify several dataset with macro

I'm trying to modify all datasets (name of datasets follow certain orders, 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 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 COMPLETE FAILED. Does anyone could give me some suggestions? Many Thanks! I'm really new for macro. 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.

Accepted Solutions
Solution
‎10-21-2016 09:20 AM
Super User
Posts: 5,353

Re: How to modify several dataset with macro

Adding to the list:

 

  • The DATA statement is missing a semicolon
  • The SET statement is missing a semicolon

Why not get a program working for just one data set, not using macros.  Once you have correct syntax for one data set, expand it using macros.

View solution in original post


All Replies
Super User
Posts: 19,038

Re: How to modify several dataset with macro

Your set statement has an extra ampersand

Your logic doesn't match code - delete removes a row not a variable. 

If bid_price is a numeric variable the period should not be in quotes. 

Solution
‎10-21-2016 09:20 AM
Super User
Posts: 5,353

Re: How to modify several dataset with macro

Adding to the list:

 

  • The DATA statement is missing a semicolon
  • The SET statement is missing a semicolon

Why not get a program working for just one data set, not using macros.  Once you have correct syntax for one data set, expand it using macros.

Super User
Super User
Posts: 7,691

Re: How to modify several dataset with macro

I really don't know where it comes from, but this is something that seems to come up several times per day, maybe its the financial sector standards or something.  Your problem is caused by the need to have many datasets with differing names, which all hold more or less the same data and contain data in the name of the dataset.  This is possibly the least efficient (in programming terms) setup you can have.  Every time you even access that data you need to go through all the loops, checks, getting the data from the filename etc. which is both resource nonsense, and prone to error.  A simple switch of your storage eradicates all those problems:

data total;
  lenght fn $200;
  set data_axpm061203900_: indsname=tmp;
  fn=tmp;
dt=input(scan(fn,2,"_"),yymmdd10.);
format dt date9.; run;

This will give you one dataset to work with, contains all your data, and if you want date then you have a column for that.  No need to write a chunk of macro to get it.  And later on, if for some reason you need to split the data out, you still have the filename, loop over that.  Simple process, simple coding, easy peasy.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 551 views
  • 0 likes
  • 4 in conversation