@togglefroggle wrote: Hey! Thank you so much for the help, I honestly really appreciate it. I can see that the way I was thinking was very Pidgeon holed so I appreciate you taking the time to explain it in an easily understandable way. In the future I will try and find the simplest solution i can instead of relying on overly complicated processes I do have one last quick question though if you do not mind, I see when you called proc format you set labels to the variable names, is there a way to do this automatically without writing each out for a large table? My codeword table is 2 columns with like 1000 different words in one column and random numbers assigned to each, is there a way to set it so that I can use the number and assign it a codeword label easily like in your proc format statements?
If you have a data set with the value pairs then with a little modification you can use it as a Cntlin optional data set with Proc Format to create the format. The Cntlin data set does have a specific format and required variable names.
The minimum variables you need to provide if it is a one to one look up like your example are:
FMTNAME: a character variable that holds the name of the format, no special characters, letters, digits and underscore only, and cannot end a number (would conflict with the w. element of format use)
Start: the value you have
Label: the text you want to display when the format is used
Type a single character of either C or N for character or numeric values.
An example using the previous values. I am creating a data set with two variables to hold the values needed. If your data set has more variables you may want to drop them when making the control set just to prevent accidentally using one of the 22 variable names that have special meaning in this context to proc format.
data list;
input value $ codeword $;
datalines;
1 PURPLE
2 BIRD
3 BRICK
4 LEFT
5 HIPPO
6 SIX
;
data codewordcontrol;
set list;
fmtname = "Codeword";
type = "C";
rename value=Start
codeword=label
;
run;
proc format cntlin=codewordcontrol;
run;
If your variables are actually numeric, not clear from your example, use Type='N' to create a format that works with numbers and don't use the $ in front of the format name when used. When your values are numeric and want to do something with a range of values such as ages of 0 to 5 you would add an End value for the end of the range and could use a label like "5 and under". There are additional variables that control whether the end points are included or excluded for the comparisons before applying the label.
You can see the other variables by adding CNTLOUT=somedataset to create the data as Proc Format uses it. The result of a cntlout data set can be used as a cntlin.
... View more