Desktop productivity for business analysts and programmers

SAS Macro Errors

Posts: 53

SAS Macro Errors

Hi ,


I have a macro defined as follows saved as a code:


%macro new (dsn1,start_month1,END_month1,months1);

proc sql;
select _NAME_,
FROM Total;

%mend new;


I am calling this macro code as :

%let dsn=AI_DB_MASTER;
%let start_month="01-JUL-2016"d; /*Starting month of 2016 the dashboard*/
%let END_month="31-JUL-2017"d;

%let months =Jul16,Aug16,Sep16,Oct16,Nov16,Dec16,Jan17,Feb17,Mar17,Apr17,May17,Jun17,Jul17;


%Include "&path.\"/source2;


But when I run the above statement I get the following error:


SYMBOLGEN: Macro variable DSN resolves to AI_DB_MASTER
SYMBOLGEN: Macro variable START_MONTH resolves to "01-JUL-2016"d
SYMBOLGEN: Macro variable END_MONTH resolves to "31-JUL-2017"d
SYMBOLGEN: Macro variable MONTHS resolves to Jul16,Aug16,Sep16,Oct16,Nov16,Dec16,Jan17,Feb17,Mar17,Apr17,May17,Jun17,Jul17
MLOGIC(NEW): Parameter DSN1 has value AI_DB_MASTER
MLOGIC(NEW): Parameter START_MONTH1 has value "01-JUL-2016"d
MLOGIC(NEW): Parameter END_MONTH1 has value "31-JUL-2017"d
MLOGIC(NEW): Parameter MONTHS1 has value Jul16
ERROR: More positional parameters found than defined.


If you notice the months1 macro only takes the first value.


Can any one help?

Super User
Super User
Posts: 9,431

Re: SAS Macro Errors

This is your problem:

%let months =Jul16,Aug16,Sep16,Oct16,Nov16,Dec16,Jan17,Feb17,Mar17,Apr17,May17,Jun17,Jul17;


Just put that text into your macro call:


Lots more paramters than expected.  


As with anything like this, is is bad data modelling that has got your into this situation.  I am guessing from the _name_ that your doing some sort of proc transpose on the data.  Don't.  Unless that data is being used for a report and only a report, keep your data normalised - i.e. going down the page.  It is so much easier to program and use.  If you continue down this route, your code will be a mess, but it is possible with %nstr() and such like functions to mask the character string.  E.g.;


Posts: 53

Re: SAS Macro Errors

Thanks. This is entirely for the purpose of building a report.However, %nrstr also doesn't solve this
Super User
Super User
Posts: 9,431

Re: SAS Macro Errors

So if this is for a report, why are we talking about this code at all:

proc transpose data=have out=want;
  by id id2;
  var xyz;
  idlabel monthvar;

proc report data=want;
  columns _name_ var:;

The proc transpose creates a transposed dataset with xyz going across, with each variable named varX where X is an incremental number and month as label, e.g.

ID   ID2    VAR1 (Jan16) VAR2 (Mar16)....


In this way you don't need the list of months, and can use shortcuts like var: i.e. all variables with var prefix.  Much simpler.

"However, %nrstr also doesn't solve this" - this doesn't really tell me anything.  Here is a working version:

%macro tmp (a=);
  %local tmp;
  %let tmp=%sysfunc(tranwrd(&a.,|,%str(,)));
  %put &tmp.;
%mend tmp;

%let list=abc|def|ghi;



Ask a Question
Discussion stats
  • 3 replies
  • 2 in conversation