topic Re: Using macro in ODS excel File name in ODS and Base Reporting

Using macro in ODS excel File name

MCB1 — Wed, 16 Sep 2020 15:12:03 GMT

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

Re: Using macro in ODS excel File name

CurtisMackWSIPP — Wed, 16 Sep 2020 15:16:08 GMT

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

Re: Using macro in ODS excel File name

ballardw — Wed, 16 Sep 2020 15:26:25 GMT

@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";

Re: Using macro in ODS excel File name

MCB1 — Wed, 16 Sep 2020 15:27:11 GMT

Thank you! I will try it!

Re: Using macro in ODS excel File name

Quentin — Wed, 16 Sep 2020 15:42:08 GMT

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

Re: Using macro in ODS excel File name

MCB1 — Wed, 16 Sep 2020 15:44:03 GMT

Thanks! this works very well!

Re: Using macro in ODS excel File name

MCB1 — Wed, 16 Sep 2020 16:18:17 GMT

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.