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.
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.
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).
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),
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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.