topic Re: Saving the last/first observation as a variable in SAS Procedures

Saving the last/first observation as a variable

BchBnz — Wed, 29 Oct 2014 14:08:02 GMT

Hello everyone,

I hope you are all well.

I am trying to find the quickest way to save the observed value of a variable "pm" at the last "time" for each "id" per "dat" as a variable.

So far I tried this code:

proc sort data=dir.sampler;

by date id time;

run;

data test;

set dir.sampler;

by date id time;

lastpm=last.pm;

run;

But it didn't work with the error "Variable lastpm uninitialized "

so I tried to at least record the last "time";

data test;

set dir.sampler;

by date id time;

lasttime=last.time;

run;

And it didn't work, and the variable lasttime had the value 0 for all the time series.

Any suggestions?

And as always, Thank you!

Re: Saving the last/first observation as a variable

RamKumar — Wed, 29 Oct 2014 14:16:59 GMT

You can use if clause with last. to derive your variable.

Replace this line lastpm=last.pm; with if last.time then variable=value;

Re: Saving the last/first observation as a variable

BchBnz — Wed, 29 Oct 2014 16:51:45 GMT

Hello RamKumar,

If I understand correctly you are suggesting to write the code like so:

data test;

set dir.sampler;

by date id time;

if last.time then lastpm=pm;

run;

In this case, sas just gives lastpm the value of pm.

What do you reckon the problem is?

Thanks

Re: Saving the last/first observation as a variable

CTorres — Wed, 29 Oct 2014 17:18:13 GMT

Try this:

data test (drop=pm);

set dir.sampler;

by date id time;

lastpm=pm;

if last.time;

run;

Re: Saving the last/first observation as a variable

esjackso — Wed, 29 Oct 2014 17:31:39 GMT

I my be misunderstanding your need, but if you need all the rows to remain and just retain the value of the last pm value then you will have to do something like:

proc sort data=dir.sampler;

by date id descending time ; /*places last time row first in the dataset*/

run;

data test;

set dir.sampler;

by date id descending time;

if first,id then lastpm = pm;

retain lastpm; /*keeps the value until changes at the next id*/

run;

I dont have anything to test this with so this is untested code.

Eric

Re: Saving the last/first observation as a variable

BchBnz — Thu, 30 Oct 2014 23:59:06 GMT

Hello Esjackso,

Thanks for you reply. However, it was retaining pm not the last observed pm.

So, based on your code, I created a new dataset with only last observations using this code:

proc sort data=dir.sampler;

by date id descending time ;

run;

data test;

set dir.sampler;

by date id descending time;

if first.id then output test;

run;

It seems to work. Thanks for your help.

Cheers!