Using macro in ODS excel File name

[MCB1]

I need to export a simple SAS table to Excel and later on import it back to SAS. I need to do it multiple times (several years). The result expected is 4 Excel files and 4 SAS files. The macro is not working well in creating the Excel files.

best regard

MCB1 

My code:

%macro bilar(ar);

ODS excel FILE='\\micro.intra\projekt\P0069_ESV$\Marcela\Bilar\OUTPUT\BESK_BILARX&ar.XLSX';

PROC FREQ DATA=G.BILAR&ar ;

TABLES BILMODELL*BESKRIVNING*PRIS/LIST MISSING ;

RUN;

ODS EXCEL CLOSE;

PROC IMPORT OUT= BESK_MODELLER&ar

DATAFILE= "\\micro.intra\Projekt\P0069_ESV$\Marcela\Bilar\OUTPUT\BESK_BILAR2X&ar.XLSX"

DBMS=EXCEL REPLACE;

RANGE="'Freq 1 - LIST Frequencies$'";

GETNAMES=YES;

MIXED=NO;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

%mend;

%bilar(2016)

%bilar(2017)

%bilar(2018)

%bilar(2019);

[CurtisMackWSIPP · Accepted solution]

Macro variables will not resolve inside single quotes.  Change them to double quotes in the ODS statement:

ODS excel FILE="\\micro.intra\projekt\P0069_ESV$\Marcela\Bilar\OUTPUT\BESK_BILARX&ar.XLSX";

[ballardw]

---

@CurtisMackWSIPP wrote:

Macro variables will not resolve inside single quotes.  Change them to double quotes in the ODS statement:

ODS excel FILE="\\micro.intra\projekt\P0069_ESV$\Marcela\Bilar\OUTPUT\BESK_BILARX&ar.XLSX";

---

And since the dot character is the macro language concatenation symbol you will need two .. before the extension.

ODS excel FILE="\\micro.intra\projekt\P0069_ESV$\Marcela\Bilar\OUTPUT\BESK_BILARX&ar..XLSX";

[MCB1]

Thanks! this works very well!

[MCB1]

Thank you! I will try it!

[Quentin]

Hi,

It looks like your goal is to get the output from PROC FREQ into a SAS dataset, is that right?

You shouldn't have to write to Excel and read it back in, to do that.  In general, I tried to avoid Excel whenever possible. : )

You should be able to use the output delivery system (ODS) to write SAS data sets directly, e.g.:

ods output list=mylist ;
proc freq data=sashelp.prdsale ;
  tables region*division*product*prodtype /missing list;
run ;
ods output close ;

proc print data=mylist ;
run ;

If you keep it all in SAS, you don't need to worry about losing precision, or having problems with SAS or Excel making bad guesses as to variable types.  ODS gives you a way to create datasets with the same information you see in the SAS output.

The scariest comment I ever saw in some code was "At this point, I exported the data into Excel and then made some fixes and read it back into SAS."

[MCB1]

Thank you Quentin! I try to avoid Excel as well. In this case I needed to create 2 variables based on information from a description (multiple strings). In my example the column "beskrivning". In Excel I can look for this particular information and create the variables in a very simple way by writing the Word or words I am looking for using the filter and later on importing it back to SAS.