topic Format issue in SAS Programming

Format issue

Babloo — Tue, 10 Sep 2019 06:05:24 GMT

After executing the code below,

proc sql;
create table dis_entity_id as select distinct ENTITY_ID  format=$ENTIT. from INSURANCE_CONTRACT;
quit;

I got Output as ,

DKV Seguros
DKV Belgien
NULL
RGO Schadn/Unfall
RGO Hestia

Now I want the Output as

DKV Seguros/DKV Belgien/NULL/RGO Schadn Unfall/RGO Hestia

so I ran the code below to get the desired output

data reqd_vars (drop=ENTITY_ID);
	set dis_entity_id end=last_record;
	by ENTITY_ID;
/*	format ENTY_NM $ENTIT.;*/
	length ENTY_NM $50;
	retain ENTY_NM;

	if first.ENTITY_ID then
		ENTY_NM=catx('/',ENTY_NM,ENTITY_ID);

	if last_record then
		output;
run;

But the Output which I got is 54009/54010/580001/58001/60011 instead of DKV Seguros/DKV Belgien/NULL/RGO Schadn Unfall/RGO Hestia. May I know why the Format is not applied in the bew variable ENTY_NM?

Re: Format issue

SASKiwi — Tue, 10 Sep 2019 06:41:05 GMT

Formats only apply when you print or view SAS variables, not when you manipulate the actual stored values with CATX and so on. If you want the formatted values actually stored in your data use the PUT function to do this:

ENTY_NM=catx('/',put(ENTY_NM, $ENTIT.),ENTITY_ID);

Edit: judging by your SQL example the format should be applied to ENTITY_ID not ENTY_NM:

ENTY_NM=catx('/',ENTY_NM,put(ENTITY_ID, $ENTIT.));

Re: Format issue

Babloo — Tue, 10 Sep 2019 06:28:44 GMT

I just wanted to inform you that both entity_id and entity_nm are character
variables.