BookmarkSubscribeRSS Feed
btkuhn
Calcite | Level 5

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!

2 REPLIES 2
Amir
PROC Star

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.

Tom
Super User Tom
Super User

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

);

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 891 views
  • 0 likes
  • 3 in conversation