12-08-2015 09:11 AM
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:
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.
12-08-2015 11:58 AM
12-08-2015 09:17 AM
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.
12-08-2015 11:44 AM
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).
12-08-2015 09:28 AM
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),
12-08-2015 10:58 AM - edited 12-08-2015 11:53 AM
You received answers to your question here:
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;
12-08-2015 11:56 AM
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.
12-08-2015 11:58 AM
12-08-2015 01:06 PM
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.
12-08-2015 01:25 PM
12-08-2015 12:09 PM
Need further help from the community? Please ask a new question.