Help using Base SAS procedures

Alphabetic prefixes for enumerated variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

Alphabetic prefixes for enumerated variables

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


Accepted Solutions
Solution
‎04-01-2015 10:42 AM
Respected Advisor
Posts: 3,777

Re: Alphabetic prefixes for enumerated variables

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


All Replies
Respected Advisor
Posts: 3,777

Re: Alphabetic prefixes for enumerated variables

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;
Frequent Contributor
Posts: 133

Re: Alphabetic prefixes for enumerated variables

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;

Solution
‎04-01-2015 10:42 AM
Respected Advisor
Posts: 3,777

Re: Alphabetic prefixes for enumerated variables

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.

Frequent Contributor
Posts: 133

Re: Alphabetic prefixes for enumerated variables

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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