BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
reneerabbit
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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

9 REPLIES 9
LinusH
Tourmaline | Level 20

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
reneerabbit
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Reeza
Super User

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;
reneerabbit
Calcite | Level 5

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.

Reeza
Super User
You won't find a faster method than an array. There was a typo, thanks to autocorrect - the function is VVALUEX.
reneerabbit
Calcite | Level 5

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.

Reeza
Super User
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.
Reeza
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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