topic Finding the first and last values in SAS Programming

Finding the first and last values

dmarques1998 — Wed, 02 Jun 2021 00:24:37 GMT

Hi,

I want to find the first and the last value of each row, giving back the variable's name, as I will try to explain in the next output:

Name	Source1	source2	source3	FirstValue	LastValue
a		A	B	Source2	source3
b	A	B		Source1	Source2
c	A		B	source1	source3
d		A	B	Source 2	source 3

Thank you.

Re: Finding the first and last values

r_behata — Wed, 02 Jun 2021 01:13:47 GMT

One Way :

data have;
	Name='a';
	source1=' ';
	source2='A';
	source3='B';
	output;
	Name='b';
	source1='A';
	source2='B';
	source3='';
	output;
	Name='c';
	source1='A';
	source2='';
	source3='B';
	output;
	Name='d';
	source1='';
	source2='A';
	source3='B';
	output;
run;

data want;
	set have;
	array src[*]  $ source:;
	do i=1 to dim(src);
		if ^ missing(src[i]) then
			do;
				FirstValue=vname(src[i]);
				leave;
			end;
	end;

	do  j=hbound(src) to 1 by -1;
		if ^ missing(src[j]) then
			do;
				LastValue=vname(src[j]);
				leave;
			end;
	end;
run;

Re: Finding the first and last values

Kurt_Bremser — Wed, 02 Jun 2021 08:23:47 GMT

This is another example where bad data structure causes one to write unnecessarily complicated code.

First, transpose your data to a long layout:

data have;
infile datalines dsd dlm=" " truncover;
input (Name source1 source2 source3) ($);
datalines;
a  A B 
b A B  
c A  B
d  A B
;

proc transpose data=have out=long (where=(col1 ne ""));
by name;
var source:;
run;

Now the exercise becomes very simple:

data want;
retain name firstvalue; /* name included to keep variable order */
set long (rename=(_name_=lastvalue));
by name;
if first.name then firstvalue = lastvalue;
if last.name;
keep name firstvalue lastvalue;
run;

Re: Finding the first and last values

dmarques1998 — Wed, 02 Jun 2021 15:29:38 GMT

It seems to be more easier. thank you. In the same example, if I have 12 different values for the same name, how can I keep only the first value?

Re: Finding the first and last values

Kurt_Bremser — Wed, 02 Jun 2021 16:10:36 GMT

@dmarques1998 wrote:
It seems to be more easier. thank you. In the same example, if I have 12 different values for the same name, how can I keep only the first value?

That's even easier, as you do not need a RETAIN:

data want;
set long (rename=(_name_=firstvalue));
by name;
if first.name;
keep name firstvalue;
run;