Hi,
I am encountering a very weird case. I am in an emergency so please help.
I first imported data from excel:
proc import out=mydataset
datafile = 'C:\mypath\myfile.xlsx'
dbms=xlsx replace;
sheet = 'sheet1';
datarow=3;getnames=no;
run;
The imported dataset assigns A,B,C,... as variable names. Their format are all characters.
I need to convert the date variable into date format, and other variables into numericals. For example, I used
data mydataset2;
set mydataset;
rename A=date B=price C=volume;
price2=input(price,12.);
run;
When I run this, I got
NOTE: Numeric values have been converted to character values at the places given by:
(Line):(Column).
7675:14
NOTE: Variable price is uninitialized.
WARNING: Variable price already exists on file WORK.SPX2.
NOTE: There were 30000 observations read from the data set WORK.SPX.
NOTE: The data set WORK.SPX2 has 30000 observations and 12 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
I could not do anything on variable formats. Moreover, I could not understand why a variable is both "uninitialized" and "already exists". This is driving me crazy. What should I do ? Thanks!
Rename statement and Rename option works little differently.
Rename statement would apply new name in the output dataset and not while processing.
I suggest either use any of the below two option
Option 1: Use data step option
data mydataset2;
set mydataset ( rename=( A=date B=price C=volume));
price2=input(price,12.);
run;
Option 2:
data mydataset2;
set mydataset;
rename A=date B=price C=volume;
price2=input(B,12.);
run;
You should do all your character to numeric conversions with input() :
data mydataset2;
set mydataset;
price = input(B, best.) ;
volume = input(C, best.) ;
date = input(A, anydtdte.) ;
format date yymmdd10. ;
drop A B C;
run;
Rename statement and Rename option works little differently.
Rename statement would apply new name in the output dataset and not while processing.
I suggest either use any of the below two option
Option 1: Use data step option
data mydataset2;
set mydataset ( rename=( A=date B=price C=volume));
price2=input(price,12.);
run;
Option 2:
data mydataset2;
set mydataset;
rename A=date B=price C=volume;
price2=input(B,12.);
run;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.