Obsidian | Level 7

## How to get the last value?

``````data have;
infile datalines truncover;
input type \$ q_20211 q_20212 q_20213;
datalines;
XX-1 1 2 3
XX-2 1
XX-3 1 3
XX-4 3 4
XX-5 4 2 3
XX-6 4
XX-7 2
XX-8 2 1 4``````

I want to add a new column named `last_value`. This column should contain the last value for every row based on the last quarter. So for example XX-1 its `last_value` is `3`.  The last value of XX-6 is `4`. This dataset is continue growing. So there is a possibilty that in quarter 4 the last_value of XX-1 changes...

1 ACCEPTED SOLUTION

Accepted Solutions
Rhodochrosite | Level 12

## Re: How to get the last value?

``````data want;
set have;
array q{*} q_:;
do i= dim(q) to 1 by -1;
if q{i} ne . then last_value=q{i};
if q{i} ne . then leave;
end;
drop i;
run;``````

If possible, I would like a few obs of large datasets to check my code works or not.

11 REPLIES 11
Rhodochrosite | Level 12

## Re: How to get the last value?

``````data want;
set have;
array q{*} q_:;
do i=1 to dim(q);
if q{i}=. then leave;
last_value=q{i};
end;
drop i;
run;``````
Obsidian | Level 7

## Re: How to get the last value?

I tried your code on a larger dataset and it doesnt seem to always work.... take a look for example to row 4 and 5 in the red box it says 2. For row 4 the last measured quarter is q_202102 and that has value 1, for row 5 the last measured quarter is q_202103 and that one also has value 1. And what about the empty ones, those should be filled to because the data is available.

Rhodochrosite | Level 12

## Re: How to get the last value?

``````data want;
set have;
array q{*} q_:;
do i= dim(q) to 1 by -1;
if q{i} ne . then last_value=q{i};
if q{i} ne . then leave;
end;
drop i;
run;``````

If possible, I would like a few obs of large datasets to check my code works or not.

Super User

## Re: How to get the last value?

Do not use wide datasets, and store dates and date-related values as SAS dates, so you can use them in calculations.

So, first convert your dataset to something that is much more useful:

``````data have;
infile datalines truncover;
input type \$ q_20211 q_20212 q_20213;
datalines;
XX-1 1 2 3
XX-2 1
XX-3 1 3
XX-4 3 4
XX-5 4 2 3
XX-6 4
XX-7 2
XX-8 2 1 4
;

proc transpose data=have out=long1 (where=(col1 ne .));
by type;
var q:;
run;

data have_long;
set long1;
rename col1=value;
quarter = input(catx("Q",substr(_name_,3,4),substr(_name_,7)),yyq6.);
format quarter yyq6.;
drop _name_;
run;``````

From that, finding the last is dead simple:

``````data want;
set have_long;
by type quarter;
if last.type;
run;``````
Obsidian | Level 7

## Re: How to get the last value?

I see your solution, its an option but I really need the quarters to stay as they are. I would like it as the answer of @japelin but then working obviously
Super User

## Re: How to get the last value?

For human consumption, you can always use PROC REPORT to display the data in a wide layout (or export it to Excel for the PHB with ODS), but for most of your work the long dataset layout is the way to go, unless you have a strong masochistic urge in you 😉

Onyx | Level 15

## Re: How to get the last value?

``````data have;
infile datalines truncover;
input type \$ q_20211 q_20212 q_20213;
datalines;
XX-1 1 2 3
XX-2 1
XX-3 1 3
XX-4 3 4
XX-5 4 2 3
XX-6 4
XX-7 2
XX-8 2 1 4
;
run;
proc print;
run;

data want;
set have;
last = coalesce(q_20213,q_20212,q_20211);
run;
proc print;
run;``````

All the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

Obsidian | Level 7

## Re: How to get the last value?

@yabwon and what if in a few months time I get the data of q_20214? I cant be changing the code every time a new quarter comes in....
Onyx | Level 15

## Re: How to get the last value?

You can always make it "macro-dynamic":

``````data have;
infile datalines truncover;
input type \$ q_20211 q_20212 q_20213;
datalines;
XX-1 1 2 3
XX-2 1
XX-3 1 3
XX-4 3 4
XX-5 4 2 3
XX-6 4
XX-7 2
XX-8 2 1 4
;
run;
proc print;
run;

proc transpose data = have(obs=0 keep=q_:) out = list(keep=_name_);
var _all_;
run;

data _null_;
length list \$ 500;
retain list;
set list end = end;
list = catx(",",_NAME_,list);
if end then call symputx("list", list, "G");
run;

options symbolgen;
data want;
set have;
last = coalesce(&list.);
run;
proc print;
run;``````

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

Obsidian | Level 7

## Re: How to get the last value?

Almost.... I need a dataset in return not a SAS report...
Super User

## Re: How to get the last value?

@Andalusia wrote:
@yabwon and what if in a few months time I get the data of q_20214? I cant be changing the code every time a new quarter comes in....

That is exactly why you use a long dataset layout; new data is then data and not structure.

Discussion stats
• 11 replies
• 1544 views
• 3 likes
• 4 in conversation