Hi SAS Experts,
I try to export my sas data set into MS Excel but encounter the following error:
Does any of you have an idea why this error occurs?
ERROR: Alphabetic prefixes for enumerated variables (ZERL-Text) are
different.
options nodate pageno=1 linesize=80 pagesize=40 orientation=landscape;
options insert=(sasautos="/sas_marko") ;
options;
%include "R:\NRKP\Scripts\13_SAS\sas_marko\bvl_sas_generate_excel.sas";
proc template;
/*HTML Style*/
define style styles.mycssstylescreen;
import "R:\NRKP\Scripts\13_SAS\sas_marko\bvl_sas_style_v01.css" screen;
end;
/*PDF Style*/
define style styles.mycssstyleprinter;
parent=styles.mycssstyle;
import "xxx.css" print;
end;
run;
/*HTML*/
ods html file="xx_sas_style_v01.css";
/*PDF*/
ods pdf file="xx_sas_style_v01.css" (print);
proc sql;
connect to oracle as nrkpdb (user=xx password=yy path=cc);
create table positivliste as
(
select *
from connection to nrkpdb
(
SELECT
SUBSTR(ADV03.TEXT1,1,( INSTR(ADV03.TEXT1,';',1)-1 )) AS "T1 ZER-Text Part 1"
,SUBSTR(ADV03.TEXT1,( INSTR(ADV03.TEXT1,';',1)+2 )) AS "T2 ZERL-Text Part 2"
FROM
ADB.DMP ADV03
)
);
disconnect from nrkpdb;
quit;
proc report data=positivliste;
title &RepTitel;
compute after ;
text = "&sysuserid: &RepFileName
%sysfunc(datetime(),datetime.) &num. Ergebnisse";
line @1 text $108.0;
endcomp;
run;
ods _all_ close;
%bvl_sas_generate_excel(positivliste, "aaalines.csv", N, );
You're on your own with that "program".
If you want to create EXCEL workbook/sheets with SAS there many ways depending on OS, and ultimate goal. Maybe you should ask for help with that.
Below is simple program that produces the error. In your program I think you are trying to create a variable not a enumerated list.
AS "T1 ZER-Text Part 1"
You need a trailing N
AS "T1 ZER-Text Part 1"n
to tell SAS that is a SASNAME. You may also need the appropriate SAS system option to use that type of name.
Hi,
thanks for the quick response.
The error comes from here:
If I comment that out, it all works fine (well, not csv file of course)
%bvl_sas_generate_excel(positivliste, "aaalines.csv", N, );
so the error comes from that macro. I found the macro online and just copied and past it. looking hours around for a macro to generate excel output.
%MACRO bvl_sas_generate_excel(LIBDSN,DOWNLOAD, VARNLABL, MOD);
FILENAME OUTFILE &DOWNLOAD LRECL=8000 RECFM=V ;
PROC CONTENTS DATA=&LIBDSN
OUT=_TEMP_(KEEP=NAME TYPE VARNUM LABEL FORMAT FORMATD FORMATL) NOPRINT;
RUN;
PROC SORT DATA=_TEMP_ OUT=_TEMP_; BY VARNUM; RUN;
DATA _NULL_; SET _TEMP_ END=EOF;
CALL SYMPUT('ZVR'||(LEFT(PUT(_N_,5.))),NAME);
CALL SYMPUT('TYP'||(LEFT(PUT(_N_,5.))),LEFT(PUT(TYPE,8.)));
IF LABEL = ' ' THEN LABEL = NAME;
CALL SYMPUT('LBL'||(LEFT(PUT(_N_,5.))),LEFT(LABEL));
CALL SYMPUT('FMT'||(LEFT(PUT(_N_,5.))),LEFT(FORMAT));
CALL SYMPUT('FMD'||(LEFT(PUT(_N_,5.))),PUT(FORMATD,BEST.));
CALL SYMPUT('FML'||(LEFT(PUT(_N_,5.))),PUT(FORMATL,BEST.));
IF EOF THEN CALL SYMPUT('TOTAL',LEFT(PUT(_N_,8.)));
RUN;
DATA _NULL_;
%DO ZI=1 %TO &TOTAL;
LENGTH TMP&ZI $40 ;
TMP&ZI = "&&FMT&ZI" || "&&FML&ZI" || "." || "&&FMD&ZI";
if compress("&&FML&ZI") = "0" then do;
TMP&ZI = "&&FMT&ZI" || ".";
end;
TMP&ZI = COMPRESS(TMP&ZI);
IF "&&FMT&ZI" = " " THEN TMP&ZI = 'BEST.' ;
CALL SYMPUT("FMT&ZI", TMP&ZI);
%END;
RUN;
DATA _NULL_;
FILE OUTFILE NOPRINT NOTITLES &MOD;
SET &LIBDSN;
FORMAT _NUMERIC_ BEST12.;
IF _N_ = 1 THEN PUT 'SEP=!';
%IF &VARNLABL EQ Y %THEN %DO;
IF _N_ = 1 THEN DO;
PUT
%DO YI =1 %TO &TOTAL;
%CMPRES("'&&ZVR&YI") +(1) '!'
%END;
+(-1)' '; /* REMOVE LAST HANGING COMMA DELIMITER */
END;
IF _N_ = 1 THEN DO;
PUT
%DO XI =1 %TO &TOTAL;
%CMPRES("'&&LBL&XI") +(1) '!'
%END;
+(-1)' '; /* REMOVE LAST HANGING COMMA DELIMITER */
END;
%END;
PUT
%DO WI=1 %TO &TOTAL;
%IF &&TYP&WI=1 %THEN %DO; /* IF NUMERIC VARIABLE */
&&ZVR&WI &&FMT&WI +(1) '!'
%END;
%ELSE %DO; /* IF CHARACTER VARIABLE */
" " &&ZVR&WI +(-1) "!"
%END;
%END;
+(-1) ' '; /* REMOVE THE EXTRA COMMA AT THE END */
RUN;
%MEND bvl_sas_generate_excel;
You're on your own with that "program".
If you want to create EXCEL workbook/sheets with SAS there many ways depending on OS, and ultimate goal. Maybe you should ask for help with that.
OK. I throw the program over board and start again.
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 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: