Hi I have the following data
I want to create a column "want" in which if TIS is equal to any of the value in columns A1 to A8. I tried the following but I did not get the value I want for "Want" column.
Data want,
set have;
if TIS=(A1--A8) then want=1;
else want=0;
run;
TIS | A1 | A2 | A3 | A4 | A5 | A6 | A7 | A8 | want |
7372 | 7323 | 0 | |||||||
5149 | 5122 | 5064 | 0 | ||||||
1541 | 4213 | 4953 | 4225 | 1541 | 5141 | 3341 | 1 | ||
3341 | 3577 | 3572 | 7372 | 7373 | 7379 | 0 | |||
2098 | 2099 | 2043 | 2041 | 2045 | 2024 | 2098 | 5149 | 5143 | 1 |
4953 | 4911 | 6719 | 0 |
Try this
data have;
input TIS A1 A2 A3 A4 A5 A6 A7 A8;
infile datalines missover;
datalines;
7372 7323
5149 5122 5064
1541 4213 4953 4225 1541 5141 3341
3341 3577 3572 7372 7373 7379
2098 2099 2043 2041 2045 2024 2098 5149 5143
4953 4911 6719
run;
data want;
set have;
array a{*} A1--A8;
if tis in a then want = 1;
else want = 0;
run;
Result:
TIS A1 A2 A3 A4 A5 A6 A7 A8 want 7372 7323 . . . . . . . 0 5149 5122 5064 . . . . . . 0 1541 4213 4953 4225 1541 5141 3341 . . 1 3341 3577 3572 7372 7373 7379 . . . 0 2098 2099 2043 2041 2045 2024 2098 5149 5143 1 4953 4911 6719 . . . . . . 0
Try this
data have;
input TIS A1 A2 A3 A4 A5 A6 A7 A8;
infile datalines missover;
datalines;
7372 7323
5149 5122 5064
1541 4213 4953 4225 1541 5141 3341
3341 3577 3572 7372 7373 7379
2098 2099 2043 2041 2045 2024 2098 5149 5143
4953 4911 6719
run;
data want;
set have;
array a{*} A1--A8;
if tis in a then want = 1;
else want = 0;
run;
Result:
TIS A1 A2 A3 A4 A5 A6 A7 A8 want 7372 7323 . . . . . . . 0 5149 5122 5064 . . . . . . 0 1541 4213 4953 4225 1541 5141 3341 . . 1 3341 3577 3572 7372 7373 7379 . . . 0 2098 2099 2043 2041 2045 2024 2098 5149 5143 1 4953 4911 6719 . . . . . . 0
With a proper data structure, this is extremely simple:
proc transpose
data=have
out=long (where=(col1 ne .))
;
by tis;
var a:;
run;
data want;
set long;
where tis = col1;
by tis;
if last.tis;
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.