DATA Step, Macro, Functions and more

Extract value of nth column with various n

Reply
Contributor
Posts: 42

Extract value of nth column with various n

[ Edited ]

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

 

 

Contributor
Posts: 21

Re: Extract value of nth column with various n

[ Edited ]

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;

Super User
Posts: 6,971

Re: Extract value of nth column with various n

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 474

Re: Extract value of nth column with various n

[ Edited ]

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

Ask a Question
Discussion stats
  • 3 replies
  • 172 views
  • 0 likes
  • 4 in conversation