Help using Base SAS procedures

Call a column name using loop

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Call a column name using loop

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!


Accepted Solutions
Solution
‎02-05-2013 09:33 AM
Super User
Super User
Posts: 7,074

Re: Call a column name using loop

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;

View solution in original post


All Replies
Super User
Posts: 19,851

Re: Call a column name using loop

Change the way you loop.

do i =10 to 200 by 10;

  want = var(i);

  Categories=i/10;

     output;

  end;

Occasional Contributor
Posts: 12

Re: Call a column name using loop

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

Super User
Posts: 19,851

Re: Call a column name using loop

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;

Occasional Contributor
Posts: 12

Re: Call a column name using loop

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

Super User
Posts: 19,851

Re: Call a column name using loop

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

Frequent Contributor
Posts: 97

Re: Call a column name using loop

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;

Regular Contributor
Posts: 195

Re: Call a column name using loop

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

Regular Contributor
Posts: 195

Re: Call a column name using loop

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

Occasional Contributor
Posts: 12

Re: Call a column name using loop

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.

Respected Advisor
Posts: 3,799

Re: Call a column name using loop

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

Solution
‎02-05-2013 09:33 AM
Super User
Super User
Posts: 7,074

Re: Call a column name using loop

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;

Occasional Contributor
Posts: 12

Re: Call a column name using loop

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

Thank you so much everyone!!!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 467 views
  • 7 likes
  • 6 in conversation