topic Using an array within a macro in SAS Programming

Using an array within a macro

btkuhn — Thu, 31 Oct 2013 17:42:31 GMT

Hi everyone,

I'm pretty new to macros in SAS and I'm stuck with a problem that seems like it should be pretty straightforward. I'm trying to read a set of excel sheets into SAS that all have a similar prefix in the filename but different suffixes, eg Temp 9-28-12, Temp 9-29-12, Temp 9-30-12. I wrote a simple macro that reads in an individual sheet:

%MACRO read_dates (date, outfile);

proc import datafile="C:\Users\Ben\Documents\Time Series 2\Data\All Phoenix Temps\Phoenix Temp &date..csv"

DBMS=CSV Out=Phoenix.&outfile REPLACE;

run;

%mend read_dates;

A sample call to this function would be:

%read_dates(9-23-13,test1);

I've tested this and it works. However, I'm having difficulties when I try to read multiple sheets consecutively. I tried creating a macro with an array consisting of different dates, which I could use a do loop to iterate through. However, I kept getting errors so I created a 'setup' macro that calls the read_dates macro, which is in turn called from a data step. For now I'm leaving aside the problem of creating differently named sas datasets, that's why I'm just renaming each set 'test1'. The code follows:

%macro setup(dt);

%read_dates(dt,test1)

%mend setup;

data dates1;

array dates {17} $ d1-d17 ('9-23-13','9-24-13','9-25-13','9-26-13','9-27-13','9-28-13',

'9-29-13','9-30-13','10-1-13', '10-2-13','10-3-13', '10-4-13', '10-5-13',

'10-6-13', '10-7-13','10-8-13','10-9-13');

do i=1 to 17;

%setup(dates{i})

end;

run;

When I run this I get the error message: There was 1 Unenclosed DO Block. I'm not sure where this is coming from. I've tried making a number of modifications without any luck. Can someone point out where I am going wrong?

Thanks!

Re: Using an array within a macro

Amir — Thu, 31 Oct 2013 18:31:59 GMT

Hi,

Generally speaking, macro code is resolved before data step code is executed, so the presence of a resolved proc import statement in the middle of the do loop in the data step will terminate the data step, giving you the error message.

Further, as the array does not exist at the time the macro is resolving you will just be passing the literal string "dates{i}" to the macro which is obviously not what you want.

One approach you can take is to set up a macro variable holding a string of all of the dates, then in your setup macro function use a macro-do loop to loop through the dates invoking the read_dates macro function.

I haven't got time to post sample code now, but it would be one way amongst many others I'm sure.

Regards,

Amir.

Re: Using an array within a macro

Tom — Thu, 31 Oct 2013 18:52:57 GMT

So you need some way to loop over the list of values.

For the method you started on to work the data step would need to generate macro calls that will execute after the data step finishes. You can use CALL EXECUTE for that.

data dates1;

array dates (17) $10 d1-d17

('9-23-13','9-24-13','9-25-13','9-26-13','9-27-13','9-28-13'

,'9-29-13','9-30-13','10-1-13','10-2-13','10-3-13','10-4-13'

,'10-5-13','10-6-13', '10-7-13','10-8-13','10-9-13'

);

do i=1 to 17;

call execute(cats('%read_dates(',dates(i),',',cats('temp',i),')' ));

end;

run;

You could also do the looping over the list in macro code instead.

%macro read_date2(datelist,prefix);

%local i date ;

%do i=1 %to %sysfunc(countw(&datelist,%str( )));

%let date=%scan(&datelist,&i,%str( ));

%read_dates(&date,&prefix.&i);

%end;

%mend read_date2 ;

%read_date2

(9-23-13 9-24-13 9-25-13 9-26-13 9-27-13 9-28-13 9-29-13 9-30-13

10-1-13 10-2-13 10-3-13 10-4-13 10-5-13 10-6-13 10-7-13 10-8-13 10-9-13

,temp

);