CODEWITHSUNDEEP
excelsas
Angel
Angel

Reputation: 11

Updating a variable in naming a file in SAS

I'm somewhat new to SAS. I'm trying to update a file name to write an excel file through a loop, but am having trouble assigning the file name. Here's my code:

%MACRO loop;
    %DO year1 = 1995 %TO 2008;
        DATA _NULL_;
            dailyret = catx(STRIP(&year1),
            '''/h1/usr11/angeli/finland/haz/phreg_dailyret_',  '.csv''');

           *to save output to excel;
           ODS TAGSETS.EXCELXP
           file= %QUOTE(dailyret)
           STYLE=minimal
           OPTIONS ( Orientation = 'landscape'
           FitToPage = 'yes'
           Pages_FitWidth = '1'
           Pages_FitHeight = '100' );

           *a block of code that runs the program, irrelevant to my question;

           ods tagsets.excelxp close;

       RUN;
    %END;
%MEND loop;

%loop;

I have tried many variations of this, but every time, I always get an error message along the lines of "ERROR: No logical assign for filename DAILYRET".

Is there any way I can do this so that I don't have to put physical quotes in the line with "file=" and be able to update the year?

Thank you so much!

-Angel

Upvotes: 1

Views: 150

Answers (1)

Tom
Tom

Reputation: 51611

To reference a macro variable prefix it with an &. Make sure to use double quote characters " to quote the filename since macro triggers are not resolved inside of single quotes.

You can also optionally append a period to the macro name so that the parser knows where the macro variable name ends and regular text starts again. This means that when you want to append an extension that starts with a period you need to have two periods since the first will be used to mark the end of the macro variable reference.

%MACRO loop;
  %DO year1 = 1995 %TO 2008;
    ODS TAGSETS.EXCELXP
        file= "/h1/usr11/angeli/finland/haz/phreg_dailyret_&year1..xml"
        STYLE=minimal
        OPTIONS ( Orientation = 'landscape'
                  FitToPage = 'yes'
                  Pages_FitWidth = '1'
                  Pages_FitHeight = '100' 
                )
    ;

*-------------------;
*a block of code that runs the program, irrelevant to my question;
*-------------------;

    ods tagsets.excelxp close;

  %END;
%MEND loop;

Upvotes: 2

Related Questions