topic Alphabetic prefixes for enumerated variables in SAS Procedures

Alphabetic prefixes for enumerated variables

metallon — Wed, 01 Apr 2015 13:35:38 GMT

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

Re: Alphabetic prefixes for enumerated variables

data_null__ — Wed, 01 Apr 2015 13:55:01 GMT

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;

Re: Alphabetic prefixes for enumerated variables

metallon — Wed, 01 Apr 2015 14:14:33 GMT

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;

+(-1) ' '; /* REMOVE THE EXTRA COMMA AT THE END */

RUN;

%MEND bvl_sas_generate_excel;

Re: Alphabetic prefixes for enumerated variables

data_null__ — Wed, 01 Apr 2015 14:42:48 GMT

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.

Re: Alphabetic prefixes for enumerated variables

metallon — Wed, 01 Apr 2015 14:53:51 GMT

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