PROC FORMAT ISSUES

[JasonNC]

Hi,

I am creating formats for look up so that i can avoid left joins which help in increasing performance.

For example based on the data i get i want to create formats

DATA TEST;

INPUT CODE $;

DATALINES;

NC

NY

AZ

;

RUN;

%MACRO FMT;

%GLOBAL CNT;

PROC SQL NOPRINT;

SELECT COUNT(*) INTO :CNT FROM TEST

WHERE CODE NE ' ' ;

QUIT;

%IF &CNT > 0 %THEN %DO;

PROC SQL NOPRINT;

CREATE TABLE SPECDESC AS

SELECT CODE AS START,DESC AS LABEL,'$DES' AS FMTNAME

FROM DESCR_TB

WHERE CODE IN (SELECT CODE FROM TEST WHERE CODE NE '' );

QUIT;

PROC FORMAT CNTLIN=SPECDESC;

RUN;

%END;

%MEND;

%FMT;

DATA TEST1;

SET TEST;

IF &CNT > 0 THEN DO;

DESCR=PUT(CODE,$DES.);

END;

ELSE DO;

DESCR=CODE;

END;

RUN;

I am getting an error when i am not having data in test

It is showing that $DES format cannot be found or load.

I want to create formats based on the data.I want to find a work around when there is no data.

[TomKari]

How about after:

PROC FORMAT CNTLIN=SPECDESC;

RUN;

%END;

insert:

%ELSE %DO;

PROC FORMAT;

VALUE $DES;

RUN;

%END;

Tom

[Ksharp]

Or add another column to specify the format type.

SELECT CODE AS START,DESC AS LABEL,'DES' AS FMTNAME,'C' AS TYPE

Ksharp