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