BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metallon
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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.

View solution in original post

4 REPLIES 4
data_null__
Jade | Level 19

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.

data _null_;
  
retain a1-b10 0;
  
run;

32         data _null_;
33            retain a1-b10 0;
ERROR:
Alphabetic prefixes for enumerated variables (a1-b10) are different.
34            run;
metallon
Pyrite | Level 9

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;

data_null__
Jade | Level 19

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.

metallon
Pyrite | Level 9

OK. I throw the program over board and start again.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 6238 views
  • 3 likes
  • 2 in conversation