SAS Macro Errors

[Lopa2016]

Hi ,

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

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

proc sql;
CREATE TABLE Total AS
select _NAME_,
&months1.
FROM Total;
QUIT;

%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.\new4.sas"/source2;
%new(&dsn,&start_month,&END_month,&months);

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?

[RW9]

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:

%new(&dsn,&start_month,&END_month,Jul16,Aug16,Sep16,Oct16,Nov16,Dec16,Jan17,Feb17,Mar17,Apr17,May17,Jun17,Jul17);

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.;

%new(&dsn,&start_month,&END_month,%nstr(&months));

[Lopa2016]

Thanks. This is entirely for the purpose of building a report.However, %nrstr also doesn't solve this

[RW9]

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;
run;

proc report data=want;
  columns _name_ var:;
run;

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;

%tmp(a=%str(&list.));