BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LoveSAS
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

12 REPLIES 12
Reeza
Super User

Change the way you loop.

do i =10 to 200 by 10;

  want = var(i);

  Categories=i/10;

     output;

  end;

LoveSAS
Calcite | Level 5

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

Reeza
Super User

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;

LoveSAS
Calcite | Level 5

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

Reeza
Super User

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

allurai0412
Fluorite | Level 6

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;

UrvishShah
Fluorite | Level 6

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

UrvishShah
Fluorite | Level 6

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

LoveSAS
Calcite | Level 5

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.

data_null__
Jade | Level 19

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

Tom
Super User Tom
Super User

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;

LoveSAS
Calcite | Level 5

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

Thank you so much everyone!!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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