topic Re: Call a column name using loop in SAS Procedures

Call a column name using loop

LoveSAS — Mon, 04 Feb 2013 19:57:42 GMT

I have a data with many columns..

ID VAR1 VAR2 VAR3 ....VAR10 .. VAR20 ... VAR30 . VAR200

1 10 15 5 .... 7 10 30 52

2 8 9 10 ... 8 9 25 5

I wanna build a new data that use only VAR10 VAR20 VAR30.... VAR200 with a new categories...

ID Categories Result

1 1 7 (from VAR10 column)

1 2 10 (from VAR20 column)

1 3 30 (from VAR30 column)

So I do something like this,

data out (keep=ID Categories Result);

set have;

length Categories $ 10;

do i =1 to 20;

want = cat('VAR',10+(i-1)*10);

Categories=cat(i);Result=want;output;

end;

run;

Somehow, it doesn't like the "want" variable cos it's a character, so it gives me the following result instead..

ID Categories Result

1 1 VAR10

1 2 VAR20

1 3 VAR30

Please help!

Re: Call a column name using loop

Reeza — Mon, 04 Feb 2013 20:08:28 GMT

Change the way you loop.

do i =10 to 200 by 10;

want = var(i);

Categories=i/10;

output;

end;

Re: Call a column name using loop

LoveSAS — Mon, 04 Feb 2013 21:40:25 GMT

somehow it said that var(i) does not have enough argument.

Re: Call a column name using loop

Reeza — Mon, 04 Feb 2013 21:51:41 GMT

var(i) is whatever your array is. I don't know how you declared it, so change var to your array and it should work.

Actually I don't see an array declaration so you'd need to add that I guess.

array var(200) var1-var200;

do i =10 to 200 by 10;

want = var(i);

Categories=i/10;

output;

end;

Re: Call a column name using loop

LoveSAS — Mon, 04 Feb 2013 21:55:06 GMT

Actually, VAR is not an array, it's the name of the column that I have. So there are total 200 columns with the name of VAR1, VAR2 ,... , VAR200

Re: Call a column name using loop

Reeza — Mon, 04 Feb 2013 21:56:15 GMT

So make it an array. Does the code not work?

Re: Call a column name using loop

allurai0412 — Tue, 05 Feb 2013 01:07:50 GMT

Hi,'

I tried in some other logic using transpose,,...but check whether it is applicable to our large....no of columns...

data have;

infile datalines;

input ID var1-var23;

datalines;

1 10 20 25 10 8 30 23 34 55 20 23 45 67 99 12 33 44 55 66 67 78 12 88 455

2 20 30 35 8 10 25 23 12 33 45 45 55 33 12 22 33 66 89 76 57 47 24 15 27

;

run;

proc transpose data=have out=out1;

by id;

run;

data out2;

set out1;

do _n_= 5 to 20; /*Here please insert as 10 to ....last column*****/

_n_=_n_+5; /****Here _n_+10****/

output;

end;

output;

run;

data allu.final;

set allu.out2;

category=compress(_name_,,'kf');

_name_=compress(_name_,,'kd');

run;

Re: Call a column name using loop

UrvishShah — Tue, 05 Feb 2013 10:49:48 GMT

Hello Love SAS,

Try the following SAS Code...

data test;

set test;

array tot_var(*) var1-var50;

do i = 1 to dim(tot_var) by 10;

categories + 1;

result = tot_var(i) || " (from " || vname(tot_var(i)) || " Column) ";

output;

end;

run;

I have kept 50 variables ranging from var1 to var50...change it as per requirements...

Thanks,

Urvish

Re: Call a column name using loop

UrvishShah — Tue, 05 Feb 2013 11:22:47 GMT

If you want the different Categories for each id then try the following code...

Data test;

retain id categories result;

set test (drop = categories);

categories = substr(scan(result,1,' '),1,1);

run;

It will give you following result...

id categories result

1 1 10 (from var10 column)

1 2 20(from var20 column)

2 1 10(from var10 column)

2 2 20 (from var20 column)

and so on...

Thanks

Re: Call a column name using loop

LoveSAS — Tue, 05 Feb 2013 14:21:25 GMT

Hi All,

I think we cannot use array. I tried it and it said that

ERROR: All variables in array list must be the same type, i.e., all numeric or character.

This is because some of the variables in my data are characters and some are numeric. They are not simply numeric values.

Re: Call a column name using loop

data_null__ — Tue, 05 Feb 2013 14:32:30 GMT

Go look at the function VVALUEX. It will allow you to lookup the "formatted value" of a variable based on the variable name.

Re: Call a column name using loop

Tom — Tue, 05 Feb 2013 14:33:35 GMT

1) Why not just use PROC TRANSPOSE?

proc transpose data=have out=want ;

by id;

var var10 var20 var30 var40 var50 var60 var70 var80 var90 var100 .... var200 ;

run;

2) Use the VVALUEX function to get the value of variable whose name you have in a variable.

data want ;

set have ;

do category = 1 to 20 ;

result = vvaluex( cats('VAR',category*10) );

output;

end;

keep id category result ;

run;

Re: Call a column name using loop

LoveSAS — Tue, 05 Feb 2013 15:02:13 GMT

Yes! vvaluex is the function that I need. It works now!

Thank you so much everyone!!!