I got an awkward data selection problem.
data have;
input x y1-y5;
datalines;
816016.2 751112,abcklop 816015,wxyp 816016,klmno
140692.5 140672,aopk 140638,bfgrt 140682,uvgt 140652,kjnh 140692,kjnh
854222.3 854442,bnghy 854222,hhgi 821004,klop 821202,bmnh
216004.2 216004,klopi 250200,brtn 772551,jklop
853332.3 874089,kjlnm 816015,qwert 816014,mnbp 853332,ubny 740210,mnbp
161002.2 716022,gmbhj 161002,dmdn 810202,yupoi 816000,klhnp
450212.3 854001,mjkopl 441406,jkoprt 450212,kmplo 751101,weqrt 816018,kjmioot
858122.2 858112,brdt 857601,olpyt 857501,kmopyth 857401,mnkop 858122,rmnhg
;
First six numbers in the row in column x matche with one of the first six numbers in the same row of column y1 through y5. But column y1 to y5 has both numbers and characters mixed, however the first six are always the numbers. I needed to select the one value (both the six numbers and attached characters) from y1 to y5 that matches the six numbers in column x and drop the rest of the unwanted values. For example,
Data want;
816016.2 816016,klmno
140692.5 140692,kjnh
854222.3 854222,hhgi
216004.2 216004,klopi
853332.3 853332,ubny
161002.2 161002,dmdn
450212.3 450212,kmplo
858122.2 858122,rmnhg
I would appreciate very much if some expert in the community can help me to get the result.
Thank you all,
SkSap
1. The Y variables cannot be numeric
2. You can write the test as
if put(x,$6.) =: vec[i] then value=vec[i];
or maybe
if scan(cat(X),1) = scan(vec[i],1) then value=vec[i];
for example, which works whether X is numeric or character.
This should work.
data want;
set have;
array vec y1-y5;
do i=1 to 5;
if substr(put(x,$6.),1,6)=substr(vec[i],1,6) then value=vec[i];
end;
keep x value;
run;
1. The Y variables cannot be numeric
2. You can write the test as
if put(x,$6.) =: vec[i] then value=vec[i];
or maybe
if scan(cat(X),1) = scan(vec[i],1) then value=vec[i];
for example, which works whether X is numeric or character.
You are right, thanks.
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.