topic Re: Extract value of nth column with various n in SAS Programming

Extract value of nth column with various n

fabdu92 — Fri, 17 Feb 2017 15:32:13 GMT

Hi all,

I have a SAS table named A like this:

ID
NUMBER
VALUE1
VALUE2
...
VALUEn

I am sure that whatever the row, the value of the column "NUMBER" is a number between 1 and n.

I want to use A to creaate a new table B which is like this

ID
VALUE

The management rule between A and B is the following:

For each of A row,

B.ID = A.ID

B.VALUE = A.VALUEi if A.NUMBER = i

Basically, if the column NUMBER from A is equal to 100, I need to get the value of the column VALUE100 and write it in the column VALUE of table B.

I precise that VALUE1 ... VALUEn are fantasy names for explanation, the real names of column don't have indexes like this.

Do you have any idea on how to do this in SAS?

Thanks

gauthamk28 — Fri, 17 Feb 2017 15:55:41 GMT

You mean u want to rotate the table and ignore the missing values of the value1,2,...n from table A and show only the columns which has values

If that's the case then this might help u , I haven't tested,

data b(keep = id value);

Set a;

do I= 1 to n;

array value{i};

If value{i} ne . then

Value=value{I};

End;

Run;

Kurt_Bremser — Fri, 17 Feb 2017 15:47:53 GMT

data b (keep=id value);
set a;
array vals{*} list of your variables;
value = vals{number};
run;

All variables in the array must be of the same type.

DanielSantos — Fri, 17 Feb 2017 15:54:08 GMT

Hi.

Not sure if got this right, but the following might work

data WANT;
      set HAVE;
      keep ID VALUE;
      VALUE=VVALUEX(cats('VALUE',put(NUMBER,best.)));
run;

VVALUEX wil return the value of the variable which it's argument evaluates.

I'm assuming NUMBER is a numeric variable, so PUT is just there to convert its numeric value into character representation.

If not, then no need for the PUT function, just replace that with NUMBER.

Hope it helps.

Daniel Santos @ www.cgd.pt