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) 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;
Change the way you loop.
do i =10 to 200 by 10;
want = var(i);
Categories=i/10;
output;
end;
somehow it said that var(i) does not have enough argument.
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;
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
So make it an array. Does the code not work?
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;
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
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
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.
Go look at the function VVALUEX. It will allow you to lookup the "formatted value" of a variable based on the variable name.
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;
Yes! vvaluex is the function that I need. It works now!
Thank you so much everyone!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.