Solved
Contributor
Posts: 30

# Looking through a row and returning a value from a range of columns

Hi,

I have the following dataset:

 Obs ID A B C 1 1 1 . . 2 1 . 1 . 3 1 1 . .

How do I make SAS look through columns A, B and C for each observation, to return a table as below:

 Obs ID A B C Category 1 1 1 . . A 2 1 . 1 . B 3 1 1 . . A

Accepted Solutions
Solution
‎02-19-2016 03:06 AM
Posts: 3,167

## Re: Looking through a row and returning a value from a range of columns

[ Edited ]

Here, after a minor tweak,

``````data have;
input Obs ID A B C ;
cards;
1 1 1 . .
2 1 . 1 .
3 1 1 . 1
;

data want;
set have;
array _t a--c;
length category \$ 100;
do over _t;
if _t=1 then category=catx('-',category,vname(_t));
/*category=vname(_t(whichn(1,of _t(*))));*/
end;
run;``````

All Replies
Super User
Posts: 13,583

## Re: Looking through a row and returning a value from a range of columns

Let's clarify the problem a little. Are you looking for the "first" variable of A B or C with ANY value or specifically the first with a value of 1?

What do you want for output if all of the values are missing?

Contributor
Posts: 30

## Re: Looking through a row and returning a value from a range of columns

Hi!

A, B and C are dummy variables. In the original dataset, each ID represents a unique patient, while A, B and C are different diagnoses.

I want to insert an additional column where the patient's diagnosis is given as a string variable. For example, if patient 1 had a value of 1 for the diagnosis B dummy, then I want a new variable that says B. If a patient has missing values for every dummy variable A, B and C, then I want the new variable to show missing as well. Similarly, if they had any combination of A, B and C, I want the new variable to show a combination: For example, if patient 1 has a value of 1 for A and B, then I want my new variable to say AB.
Contributor
Posts: 38

## Re: Looking through a row and returning a value from a range of columns

[ Edited ]
``````data have;
input id:\$1. A B C;
cards;
1 1 . .
1 . 1 .
1 1 . .
;
run;
data want(drop=str key lastcat);
format key \$8. category \$1.;
if _n_ = 1 then do;
lastCat = 0;
declare hash lu();
lu.defineKey('key');
lu.defineData('category');
lu.defineDone();
end;
set have;
array vars {3} A B C;
str = 'ABCDFGHIJKLMNOPQRSTUVWXYZ';

key=catx(',',of vars{*});
if lu.find()=0 then output;
else do;
lastCat + 1;
category = substr(str,lastCat,1);
output;
end;
run;``````

The question of what the category value should be is a little tricky. If it has to be a letter, then this code will work for up to 26 categories.

It would be easiet if the category values were numbers, in which case you would just change category = lastCat instead of substr(), and remove the str variable creation.

Contributor
Posts: 30

## Re: Looking through a row and returning a value from a range of columns

Hi!

The categories refer to different diagnoses in my original dataset. These are made up of one letter followed by two digits, e.g. L40, M06, K50.
Contributor
Posts: 38

## Re: Looking through a row and returning a value from a range of columns

Do you require the category values to be letters?
Contributor
Posts: 30

## Re: Looking through a row and returning a value from a range of columns

No, I only need to be able to categorize the observations based on diagnosis.
Contributor
Posts: 38

## Re: Looking through a row and returning a value from a range of columns

And, I'm assuming that you're not concerned with the order of the diagnosis? Ie. the variables A and B with values L01/M04 would be the same as M04/L01.
Contributor
Posts: 30

## Re: Looking through a row and returning a value from a range of columns

The variables A and B are dummy variables. In my original dataset, these variables have names like L01 and M04, but the actual values are either 1 or "."
Posts: 3,167

## Re: Looking through a row and returning a value from a range of columns

The following code choose the first '1' if you have multiple, tweak it to accomodate your real data&colon;

``````data have;
input Obs ID A B C ;
cards;
1 1 1 . .
2 1 . 1 .
3 1 1 . .
;

data want;
set have;
array _t a--c;
length category \$ 10;
category=vname(_t(whichn(1,of _t(*))));
run;``````
Contributor
Posts: 30

## Re: Looking through a row and returning a value from a range of columns

Hi!

In my original dataset, an observation can have a value of 1 in more than one of the columns. In that case, I need SAS to return the name of both columns where the observation has a value of 1.
Solution
‎02-19-2016 03:06 AM
Posts: 3,167

## Re: Looking through a row and returning a value from a range of columns

[ Edited ]

Here, after a minor tweak,

``````data have;
input Obs ID A B C ;
cards;
1 1 1 . .
2 1 . 1 .
3 1 1 . 1
;

data want;
set have;
array _t a--c;
length category \$ 100;
do over _t;
if _t=1 then category=catx('-',category,vname(_t));
/*category=vname(_t(whichn(1,of _t(*))));*/
end;
run;``````
Contributor
Posts: 30

## Re: Looking through a row and returning a value from a range of columns

This solved the problem. Many thanks for your help!
Contributor
Posts: 38

## Re: Looking through a row and returning a value from a range of columns

``````data have;
input
id:\$1.
M01 : 1.
M02 : 1.
L01 : 1.;
cards;
A 1 . .
B 1 . 1
C 1 . .
D . 1 1
E 1 . 1
;
run;
data want(drop=key lastcat);
format key \$60.;
if _n_ = 1 then do;
lastCat = 0;
declare hash lu();
lu.defineKey('key');
lu.defineData('category');
lu.defineDone();
end;
set have;
array vars {3} M01 -- L01;
key=catQ('a',',',of vars{*});
if lu.find()=0 then output;
else do;
lastCat +1;
category = lastCat;
output;
end;
run;``````

Ok, I think this is what you're describing.

🔒 This topic is solved and locked.