CA | Item | CA1 | SAL |
12356 | NO | 856 | YES |
34527 | YES | 245 | 2.56 |
56789 | 1 | 123 | 3.2 |
hI ,
i Have a dataset like above and i am trying to transpose the dataset but i have colums which are only numeric and other columns which are alphanumeric so how can i convert all variables (i have hundreds of variables ) to character at once so that i can use sigle proc transpose for the dataset
Can anyone pls help Thanks
What are you looking to get as output?
Remember a column in SAS can hold only one type of variable, a character or a numeric variable.
I am looking to have CA and CA1 also to be character but i have 100's of columns in my dataset so how can i chnage all columns into character
Thanks
You can reference all numeric columns using _numeric_, but you need to know how many because you need to create new variables to hold the character values. If you're transposing, it maybe better to transpose and convert at once.
data want;
set have;
array nums(*) _numeric_;
array chars(*) _character_;
do i=1 to dim(nums);
variable_name=vname(nums(i));
value = put(nums(i), 8.);
output;
end;
do i=1 to dim(chars);
variable_name=vname(nums(i));
value = chars(i);
output;
end;;
run;
Convert all numeric variables to character at once
Can also do using datastep _numeric_ array but may need to rename
R can also do this for you
https://goo.gl/zpYfS4
https://communities.sas.com/t5/SAS-Procedures/convert-all-numeric-variables-to-charater-at-once/m-p/314934
HAVE
===
Up to 40 obs WORK.HAVE total obs=3
Obs CA ITEM CA1 SAL
1 12356 NO 856 YES
2 34527 YES 245 2.56
3 56789 1 123 3.2
-- CHARACTER --
ITEM C 8 NO
SAL C 8 YES
TOTOBS C 16 3
-- NUMERIC --
CA N 8 12356
CA1 N 8 856
WANT ( I did not convert CA because it looks like a key?)
===========================================================
Up to 40 obs WORK.HAVXPOXPO total obs=3
Obs CA ITEM CA1 SAL
1 12356 NO 856 YES
2 34527 YES 245 2.56
3 56789 1 123 3.2
Variables in Creation Order
# Variable Type Len
1 CA Num 8
2 ITEM Char 12
3 CA1 Char 12
4 SAL Char 12
WORKING CODE
============
proc transpose data=have out=havxpo;
by ca;
var _all_;
run;quit;
FULL SOLUTIONS
==============
* Create some data;
* FYI - I compress out blanks with one function key;
data have;
input
CA Item $ CA1 SAL $;
cards4;
12356 NO 856 YES
34527 YES 245 2.56
56789 1 123 3.2
;;;;
run;quit;
FULL SOLUTION
=============
proc transpose data=have out=havxpo;
by ca;
var _all_;
run;quit;
/*
Up to 40 obs WORK.HAVXPO total obs=12
Obs CA _NAME_ COL1
1 12356 CA 12356
2 12356 ITEM NO
3 12356 CA1 856
4 12356 SAL YES
5 34527 CA 34527
6 34527 ITEM YES
7 34527 CA1 245
8 34527 SAL 2.56
9 56789 CA 56789
10 56789 ITEM 1
11 56789 CA1 123
12 56789 SAL 3.2
*/
proc transpose data=havxpo out=havxpoxpo(drop=_name_);
by ca;
id _name_;
var col1;
run;quit;
/*
Up to 40 obs WORK.HAVXPOXPO total obs=3
Obs CA ITEM CA1 SAL
1 12356 NO 856 YES
2 34527 YES 245 2.56
3 56789 1 123 3.2
*/
Here's an alternative: run two proc transposes, one for numerics (naming vars NUM1-NUMn) and one for character (naming vars CHAR1-CHARn), where n is the number of observations in the source data set. Each of the result files will get the same number of variables (n, excluding the new variable _NAME_).
Then just SET them both, and convert NUM1-NUMn to character form in CHAR1-CHARn. Drop the NUM: vars. No need to determine how many variables of character vs numeric there are:
proc transpose data=sashelp.class out=tn prefix=num;
run;
proc transpose data=sashelp.class out=tc prefix=char;
var _character_;
run;
data want;
set tn (in=in_n) tc;
array n {*} num: ;
array c {*} char: ;
if in_n then do I=1 to dim(n);
c{I}=put(n{I},8.);
end;
drop num: I ;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: