I need to create a one line per customer file that creates 8 'monthly premium quote' variables based on a persons gender and age (we create quotes for smokers and non-smokers as well but we don't have this in our dataset). So I have the data file that looks like:
cust_num age_yrs gender
1 33 F
I was wondering if there is a way to pull in my 'lookup table' and create the quotes I need. I was thinking of transposing my look up table but then I still need to use it with my data to set up the new variables. I was also thinking I might be able to do this with formats somehow? Any advice would be greatly appreciated!
Yes, PROC FORMAT (using CNTLIN=) would work creating a numeric format for your age range (START and END variables) and the LABEL variable will be a character string with your "match condition" values. When you find a match using the PUT function in a DATA step, you will want to use the INPUT function and the SCAN function to parse the LABEL (returned by the PUT function) and convert the sub-fields to numeric.
Have a look at the SAS support http://support.sas.com/ website and search on phrase "PROC FORMAT" and include the CNTLIN keyword -- you prepare a SAS file with specific-named SAS variables (FMTNAME, START, END, HLO, LABEL) and pass the file to PROC FORMAT using CNTLIN=, which generates the format from your "quote table" data.
As I mentioned, use the PUT function in an assignment statement to retrieve the results from your format, based on age range. Code snippet shown below:
length fmtdata $nnn ; /* max size to hold data returned from format / put */
fmtdata = put(age,fmtname.);
if fmtdata ne '?' then do; /* test for OTHER='?' condition */
* code below presumes that the FORMAT RESULT variable is format with ;
* some number of sub-fields, separated by a blank, and using numeric ;
* length nnn when the format is generated. ;
f_s = input(scan(fmtdata,1,' '),nnn.);
f_ns = input(scan(fmtdata,2,' '),nnn.);
* at this point you have numeric variables with quote info from format. ;
Did you debug the CNTLIN= data file to confirm the SAS error diagnostic being true? That would be the first step. Yes, the presumption is that you have discrete non-overlapping START and END values to populate your format. Per the SAS documentation, this is a requirement, unless you are using other PROC FORMAT features beyond the scope of this post.
Also, your LENGTH statement is unnecessary - anyway, if it did matter for numeric variable granularity, it would need to be coded ahead of the SET statement, otherwise your input variables "could" be truncated, depending on the declared length. Though not germaine to this post, regardless it's a SAS programming habit for you to consider.
I suggest you analyze your input data, focusing on the critical variables FMTNAME, HLO, START and END.