Hi Community,
I have a table like this:
AK_2014 | AK_2015 | AK_2016 | AK_2017 | AK_2018 | AK_2019 |
---|---|---|---|---|---|
0 | 100 | 0 | 0 | 200 | 0 |
50 | 0 | 0 | 30 | 0 | 10 |
25 | 0 | 0 | 0 | 17 | 0 |
I would like to create a data step and have to access the columns depending on the input date. For example:
Data test;
set AK_Table;
y=year(input_date);
If AK_y >0 then;
Result1=AK_y;
Else if AK_(y-1) >0 then;
Result1=AK_(y-1);
.....
run;
But obviously this is not the correct way, since SAS tries to find the variable AK_y, which does not exist.
How can I get the value of y and access the correct variable? For example if y=2015, then access AK_2015 and (if necessary) AK_2014, if y=2018, access AK_2018 and (if necessary) AK_2017 and so on...
Thank you in advance for any help.
Hi,
Arrays are probably the easiest. I would suggest that you remove the 201x from your variable names and just use a sequential number with the year in the label, it will make your life easier and your code look better.
data want;
set have;
array ak{6} 8. ak_2014-ak2019;
y=year(input_date);
result=ak{y-2013};
run;
Now I haven't checked the above runs as I don't have test data, but what it does is take 2013 off your year value to leave 1-9 and use that to reference the specific variable in the array. The reason I mention sequential numbers is that if you have a date with 2013 then your going to have problems. If its just 1-9 and 1 references the first date in the row, then the code would work in all cases, and you then just need to check the label for ak_1 to get the year.
data have;
input a_2014 a_2013 ;
cards;
1 4
0 7
5 8
;
run;
%let YEAR = 2014;
%let PYEAR = %sysfunc(putn(&YEAR-1,4.));
data want;
set have;
if a_&YEAR > 0 then result1=a_&YEAR;
Else if a_&PYEAR > 0 then result1=a_&PYEAR;
run;
Is input_date a simple value outside the dataset OR it is a variable include in the dataset ?
Xia Keshan
input_date is a variable the user has to input via prompt in SAS.
So you are using EG . better post is at SAS Enterprise Guide.
And mohamoed's code might be what you are looking for .
Ok, I'll do this next time.
Ok, but I also liked the suggestion of RW9 and used an array since I don't have values before 2014.
Thank you all guys for your help. You are simply awesome.
Then, please mark the question as answered.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.