topic Re: How to get the last value? in SAS Enterprise Guide

How to get the last value?

Andalusia — Wed, 01 Sep 2021 08:37:20 GMT

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

Re: How to get the last value?

japelin — Wed, 01 Sep 2021 08:46:34 GMT

how about this code?

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;

Re: How to get the last value?

Kurt_Bremser — Wed, 01 Sep 2021 08:51:57 GMT

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;

Re: How to get the last value?

Andalusia — Wed, 01 Sep 2021 09:11:06 GMT

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.

Re: How to get the last value?

yabwon — Wed, 01 Sep 2021 09:13:12 GMT

How about that:

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

Re: How to get the last value?

Andalusia — Wed, 01 Sep 2021 09:13:20 GMT

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

Re: How to get the last value?

Andalusia — Wed, 01 Sep 2021 09:14:49 GMT

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

Re: How to get the last value?

Kurt_Bremser — Wed, 01 Sep 2021 09:18:31 GMT

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 😉

Re: How to get the last value?

yabwon — Wed, 01 Sep 2021 09:23:51 GMT

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

Re: How to get the last value?

japelin — Wed, 01 Sep 2021 09:25:00 GMT

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.

Re: How to get the last value?

Andalusia — Wed, 01 Sep 2021 09:25:52 GMT

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

Re: How to get the last value?

Kurt_Bremser — Wed, 01 Sep 2021 09:25:53 GMT

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