topic Re: SAS Format from dataset seperated by character in SAS Programming

SAS Format from dataset seperated by character

sasuser101 — Tue, 25 Apr 2017 14:26:45 GMT

i am rewriting the question. apologize for creating confusion

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

	Code	Description
1	abc	this is for value a
2	bgcbs	contact customer
3	jfhfg	wait for response
4	shdg	should be ready
5	mi	made indoor
80	ni	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	this is for value a : wait for response : made indoor
b				shdg		ni	shdg : ni	should be ready : not indoor
c		bgcbs			mi		bgcbs : mi	contact customer : made indoor
d	abc		jfhfg				abc : jfhfg	this is for value a : wait for response
e		bgcbs		shdg	mi		bgcbs : shdg : mi	contact customer : should be ready : made indoor
f				shdg		ni	shdg : ni	should be ready : not indoor

Thanks

Re: SAS Format from dataset seperated by character

ballardw — Mon, 24 Apr 2017 22:38:56 GMT

This may give a starting point:

proc format library=work;
value $abc
'abc'=' this is for value a '
'bgcbs'=' contact customer '
'jfhfg'=' wait for response '
'shdg'=' should be ready '
;
run;

data example;
   str = 'abc:abc:bgcbs:jfhfg:shdg';
   length newstr $ 200;
   newstr=str;
   do i = 1 to (countw(str,':'));
      newstr = tranwrd(newstr,scan(newstr,i,':'),put(scan(newstr,i,':'),$abc.));
   end;
   drop i;
run;

Note that you will need to determine a maximum length for the target variable newstr to allow for the total increased length of the inserted characters. If you starting strings have 15 codes and may be up to 20 letters in the value then including : and spaces you'll likely want it to be around 350 or so characters.

Multilable isn't going to help here. It might if you pulled each code into a separate variable but I'm having a hard time seeing what that may be without an actual multi label value example.

Re: SAS Format from dataset seperated by character

sasuser101 — Mon, 24 Apr 2017 22:44:54 GMT

Thank you for response. i apologise i forgot to mention i have upto 80 columns with codes and description. how would it work in that case when creating format ?

Re: SAS Format from dataset seperated by character

ballardw — Mon, 24 Apr 2017 23:00:08 GMT

@sasuser101 wrote:

Thank you for response. i apologise i forgot to mention i have upto 80 columns with codes and description. how would it work in that case when creating format ?

You might have to provide a bit more detail. Are you saying that each column within that combined string would have a different format needed?

Or just that you have a lot of codes that happen to be in a file with a layout like this:

Code <code meaning> code <code meaning

If you need to apply different formats/rules to different parts of the combined string then thats another issue and possibly very complex.

Re: SAS Format from dataset seperated by character

Reeza — Tue, 25 Apr 2017 00:12:56 GMT

You can create a format from a dataset, you don't need to write it out manually. Search for a paper titled 'Not just another pretty face' that has a great walk through of PROC FORMAT with examples.

Re: SAS Format from dataset seperated by character

sasuser101 — Tue, 25 Apr 2017 14:52:20 GMT

I have updated the post. Sorry for confusion.

Re: SAS Format from dataset seperated by character

Reeza — Tue, 25 Apr 2017 17:21:43 GMT

@ballardw solution is correct, create the format from table 1 using CTNLIN table with PROC FORMAT.

See example 8 on page 10 here:

http://www2.sas.com/proceedings/sugi30/001-30.pdf