topic Re: How to select variable (with a year in the name) for calculation based on valued of a date field in SAS Data Management

How to select variable (with a year in the name) for calculation based on valued of a date field

reneerabbit — Tue, 08 Dec 2015 14:11:21 GMT

With SAS SQL (or just SAS) I need to use a variable for a calculation based on the year portion of a different date field. The variable's name contains the year that I'd need to match from the year portion of the other date variable. How can I select the right variable to use for my calculation?

For example, I need to select which one of these to use:

GRADE_2013

GRADE_2014

GRADE_2015

by looking at a date field of the format '15JAN2014'd - so from that year of 2014 I want to grab the value from GRADE_2014 to use in another calculation.

Re: How to select variable (with a year in the name) for calculation based on valued of a date field

LinusH — Tue, 08 Dec 2015 14:17:28 GMT

Not knowing what kind of calculating you wish to make, but usually data and calculations benefit from having data in normalized fashion.

In your case, it means transposing from wide to long:

id year Grade

1 2013 A

1 2014 B

1 2015 A

Describing your whole business requirement, and full layout of your dataset helps to get adequate answers.

Re: How to select variable (with a year in the name) for calculation based on valued of a date field

RW9 — Tue, 08 Dec 2015 14:28:10 GMT

I totally agree with LinusH here. You have put "data" into column headings. This will cause you problems with anykind of processing you do on that data. Separate your processing data (with data in columns) and report data (transposed data with data in headers),

Re: How to select variable (with a year in the name) for calculation based on valued of a date field

Reeza — Tue, 08 Dec 2015 16:53:47 GMT

You received answers to your question here:

http://stackoverflow.com/questions/34076789/how-to-select-variable-with-a-year-in-the-name-for-calculation-based-on-value

Does the answer not work for you somehow? If not, please post how.

Answer reposted here:


Data One;
set Have;
array grades(2013:2015) grade_2013-grade_2015;

*Array method;
variable_want1 = grades(year(date_field));

*VValueX method;
variable_want2 = vvaluex('grades_'||put(year(date_field), 4.));
run;

Re: How to select variable (with a year in the name) for calculation based on valued of a date field

reneerabbit — Tue, 08 Dec 2015 16:44:54 GMT

Sorry - I was trying to simplify and abstract the issue. Here is more detail and the exact question I'm trying to answer - it differs a bit from my contrived example:

I've constructed a table that consists of a row for each individual sale that includes:
-the date sold and
-the date when service on that product sale ended/terminated.

Also appended from another source are:
-the characteristics of the person making the sale and
-the characteristics of the person providing service on the product.

The salesperson/service provider characteristics consist of a two categorical variables that are added annually: basically two different award categories for each of the last 15 years - 30 columns of data at this point.

My task is to find the relationship, if any, of the award levels at the time of the sale and the time of the service end date. I have those dates ('31jan2013'd) but I have to then relate those to the correct year of the salesperson's award characteristics. So if the sale was made in 2013 then I need to know the value of AWARD1_2013 (picking it from AWARD1_2000 through AWARD1_2015). The same thing for AWARD2_2013. The process is similar for the service termination date and the characteristics at that year date.

This is currently a table of more than 500k sales (rows) and 100 columns (sales and salespersons/service provider characteristics).

Re: How to select variable (with a year in the name) for calculation based on valued of a date field

reneerabbit — Tue, 08 Dec 2015 16:56:56 GMT

I'm not a programmer by trade and I have no knowledge of how to use arrays...and when I tried to use VVALUES I got an error that "the function VVALUES is unknown" leading me to think that my version/setup of SAS can't use the VVALUES function. So I'm looking for alternatives that hopefully I can implement more quickly.

Re: How to select variable (with a year in the name) for calculation based on valued of a date field

Reeza — Tue, 08 Dec 2015 16:58:54 GMT

You won't find a faster method than an array. There was a typo, thanks to autocorrect - the function is VVALUEX.

Re: How to select variable (with a year in the name) for calculation based on valued of a date field

Reeza — Tue, 08 Dec 2015 17:09:42 GMT

Arrays in SAS are shortcut references to variables. You create an array by declaring an array, stating the dimensions and then listing the variables that are references, grade2012-2014. You can index an array with years, instead of 1-n. So if
I declare an array

array grades(2014:2016) grades2014-grades20156;

Then I can reference the variables as
grades(2014) -> grades2014 variable
grades(2015) - > grades2015 variable
grades(2015) -> grades 2016 variable.

Here's a paper that does a quick introduction to arrays.

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

Re: How to select variable (with a year in the name) for calculation based on valued of a date field

reneerabbit — Tue, 08 Dec 2015 18:06:44 GMT

Thanks for pointing out VVALUEX - that seems to be working - transposing with 500k records seems unwieldy and learning arrays "in the heat of the battle" is not optimal at the moment.

Re: How to select variable (with a year in the name) for calculation based on valued of a date field

Reeza — Tue, 08 Dec 2015 18:25:07 GMT

One reason not to use VVALUEX is that it returns a character variable instead of numeric. You may need to account for this in the further processing of your data.