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
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???
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)?
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
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.
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???
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?
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.
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
Thank you for your reply i will try this method as well.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: