proc format separted by semi colon

[sasuser101]

i am rewriting the question posted previously in new post.

i have following lookup table that as code and description. i have approximately 80 rows in lookup table.

	Code	Description
1	abc	1-this is for value a
2	bgcbs	2-contact customer
3	jfhfg	3-wait for response
4	shdg	4-should be ready
5	mi	5-made indoor
80	ni	6-not indoor

 i have created Combined Codes column using array. i am trying to convert Combined Codes column like Combined description by applying format from above lookup table. Combined Codes are seperated by ' : ' .

Customer name	abc_check	bgcbs _check	jfhfg_check	shdg_check	mi_check	ni_check	Combined Codes	Combined Description
a	abc		jfhfg		mi		abc  : jfhfg : mi	1-this is for value a : 3-wait for response :  5-made indoor
b				shdg		ni	shdg : ni	4-should be ready :  6-not indoor
c		bgcbs			mi		bgcbs : mi	2-contact customer : 5-made indoor
d	abc		jfhfg				abc  : jfhfg	1-this is for value a :   3-wait for response
e		bgcbs		shdg	mi		bgcbs : shdg : mi	2-contact customer : 4- should be ready : 5-made indoor
f				shdg		ni	shdg : ni	4-should be ready :  6-not indoor

Thanks

[Astounding]

So where do you need help?

Are you able to create a format from the lookup table (or is that causing trouble)?

If you had a format, would  you be able to create the combined column of descriptions (or is that causing trouble)?

[sasuser101]

Thanks for your reply. i need help with

1) Create format using table

2) Apply format toCombined Codes column, so it looks like Combined description

[Reeza]

I posted a link in your previous post and @ballardw solution in that post is exactly what I recommend as well and the most efficient. 

[ballardw · Accepted solution]

Please examine:

data cntlin;
   set codes;
   start=code;
   end=code;
   label=description;
   Fmtname= 'abc';
   type='C';
;
run;

proc format library=work cntlin=cntlin;
run;

data want;
   set have;
   array v abc_check bgcbs_check jfhfg_check  shdg_check  mi_check ni_check ;
   length combineddescription $ 200;
   do i=1 to dim(v);
      combineddescription = catx(':',combineddescription,put(v[i],$abc.));
   end;
run;

The CNTLIN data set used by Proc format will require several variables and the names must be: Fmtname, start, end, label and type.

Fmtname is the name of the format you want to use, start is the beginning value for a range, end is the end value for a range with a single valid then set both the value, label is the text to display and type is designate the format type C=Character or N=Numeric.

there are other options but not needed here.

Do you really need the ugly "combined" varaible when you have the description???

[sasuser101]

Thanks your reply. Can i use your solution to just apply format to Combined Code column? in other words can i replace code with description in Combined Code column by applying format?

[ballardw]

IF you create that other variable then the method is in the other question you posted.

If you actually need that other variable then add a combinedcode variable to the length statment and create it the same way as the combined description.

There really is absolutely no advantage to creating your combined code variable first and then playing games with pulling a varaible apart to replace things.

[novinosrin]

I trust you have written a nice code to get the combined codes using array and are only looking for combined description. If your datasets are exactly like the sample, here is one easy solution:

data want;

if _n_=1 then do;

if 0 then set have;

   declare hash h(dataset:'your_lookup_table');

   h.definekey('code');

   h.definedata('description');

   h.definedone();

   end;

   set your_combined_codes_table;

   length combined_description $50;

   array ch(*)abc_check--ni_check;

   do _n_=1 to dim(ch);

   if not missing(ch(_n_)) then do;

   temp=ch(_n_);

      rc=h.find(key:temp);

   combined_description=catx(':',combined_description,description);

   end;

   else continue;

   end;

   drop temp;

run;

I am too lazy and tired to test at 12:05AM past midnight in Chennai. I hope that works for you.

Regards,

Naveen Srinivasan

[sasuser101]

Thank you for your reply i will try this method as well.