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.
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;
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;
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 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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.