SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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

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.


Accepted Solutions
Solution
‎12-08-2015 01:04 PM
Super User
Posts: 17,840

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

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

View solution in original post


All Replies
Super User
Posts: 5,257

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

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.

Data never sleeps
Occasional Contributor
Posts: 6

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

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).

Super User
Super User
Posts: 7,405

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

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),

Super User
Posts: 17,840

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

[ Edited ]

You received answers to your question here:

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

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;
Occasional Contributor
Posts: 6

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

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.

Solution
‎12-08-2015 01:04 PM
Super User
Posts: 17,840

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

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

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

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.

Super User
Posts: 17,840

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

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.
Super User
Posts: 17,840

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

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
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 553 views
  • 0 likes
  • 4 in conversation