- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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???
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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)?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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???
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for your reply i will try this method as well.