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?
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.));
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.));
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.