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);
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";
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";
@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";
Thank you! I will try it!
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."
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.