topic Re: Data manipulation: How do I select row values in SAS Programming

Data manipulation: How do I select row values

sijansap — Wed, 12 Aug 2020 01:27:05 GMT

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

Re: Data manipulation: How do I select row values

Angel_Larrion — Wed, 12 Aug 2020 02:13:04 GMT

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;

Re: Data manipulation: How do I select row values

ChrisNZ — Wed, 12 Aug 2020 03:10:00 GMT

@Angel_Larrion

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.

Re: Data manipulation: How do I select row values

sijansap — Wed, 12 Aug 2020 03:28:02 GMT

Thank you ChrisNZ. The one with put worked but it left one of the value. The one with scan worked perfect. Thanks again.

Re: Data manipulation: How do I select row values

sijansap — Wed, 12 Aug 2020 03:28:30 GMT

Thank you Angel_Larrion, it worked but one of the values did not show up rest came out fine.

Re: Data manipulation: How do I select row values

Angel_Larrion — Wed, 12 Aug 2020 03:31:11 GMT

You are right, thanks.