BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dmarques1998
Obsidian | Level 7

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: 

 

NameSource1source2source3FirstValueLastValue
a AB

Source2

source3
bAB Source1Source2
cA Bsource1source3
d ABSource 2source 3

 

Thank you. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

4 REPLIES 4
r_behata
Barite | Level 11

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;
Kurt_Bremser
Super User

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;
dmarques1998
Obsidian | Level 7
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?
Kurt_Bremser
Super User

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1173 views
  • 2 likes
  • 3 in conversation