Number var becomes String with Import

[epidtech]

Hi,

I've been having a hard time with my new computer system that's why I have all the posts.

Anyway, I'm importing a .dbf using the import wizard and some of my date variables are being changed from datetime to character. This confuses sas when I run datasteps for coding.

I attempted to change the format in the datastep for example;

DATA WORK.ANALYSIS;
ATTRIB CLOSED_DT FORMAT=MMDDYY8.;
SET WORK.ANALII;
CODE;
CODE;
CODE;
CODE;
RUN;

When I run this I get the error:
ERROR: Variable CLOSED_DT has been defined as both character and numeric.

When I look at the original .xls I have it formated as a date. Then I have to save it as a .dbf (don't ask why it's a long story) where I'm assuming it is understood that it's a number, but when I look at the contents of the SAS file after import but before the coding datastep it reads as a character.

Any thoughts?

Thanks in advance.

 

[klaz2002]

epidtech,
I know that new machines are hard to work on. Things don't always work with no apparent reason. The format you gave was for a numeric var. SAS thinks that the CLOSED_DT var is numeric, yet you just wrote that the conversion changes that var to char. Perhaps you can change/add the informat for the appropiate char date. This should convert the text into a sas date. Just make sure that your code doesn't try to create a numeric & char variable with the same name (in the same dataset).
If this process has worked in the past, you could look to the reason that the 'dbf' conversion is not working correctly (the way it used to work).
But, I think the first way is easier to do and faster.
klaz

 

[epidtech]

Hi Klaz,
I greatly appreciate the advice, but I'm a little confused. When I import the file into SAS before I've done any coding SAS thinks the CLOSED_DT is a char variable. What would the code be to change the variable from a string to a number? When I try to do it directly using properties in the dataset (i.e. in insight) it erases all the values and I have to reimport the file. I am currently running the dataset without touching that variable but I occasionally have to select out cases with missing CLOSE_DT values so I have to do a where statement (WHERE CLOSED_DT NOT=" ", thus I know it's a string.

Again I appreciate all the help!

 

[klaz2002]

epidtech,
Once you have your imported data in a SAS dataset you could change the var type as follows. 1)You rename the var in question (CLOSED_DT --> CLOSE_DTc). 2)You use an informat to create the numeric var.
ex.
data your_new_data;
set ANALYSIS (rename=(closed_dt=close_dtc));
close_dt = input(close_dtc,your_informat_here[mmddyy10.]);
**THE TYPE OF INFORMAT DEPENDS ON WHAT THE DATE STRING LOOKS LIKE. SEE SAS HELP FOR MORE INFO;
run;

If you don't mind leaving it in the char var, your 'where' statement should change slightly. Use a TRIM function to trim trailing spaces. Also use the LEFT function to left align the date in the string.
ex. close_dt = trim(left(close_dt));
Then you can use the where statement that you wrote. With out the extra functions, a record with some blank spaces in it won't kick your statement off. (meaning evaluate to TRUE)

Hope this helps.
Klaz

 

[klaz2002]

epidtech,
One other thing why not use the NE operator in your where statement (instead of NOT=)? Its more readable to others (looking over your code many eons from now).
Klaz

 

[epidtech]

Well I'll be damned. It worked! Thanks! It gave me 3 variables for closed_dt (closed_dt, closed_dtc and closed_dt2) which I didn't realize would happen but that's cool. All of the errors I was having are not gone.

This worked really well! Thanks!

 

[epidtech]

OK, I feel silly. In my excitement I forget that I had a variable created closed_dt2 and it was just working.

I'll have to play with this somemore to select the correct format because neither mmddyy8. nor mmddyy10. work but it's a good start!

 

[klaz2002]

You can always access the SAS help online @ the following location.

http://v8doc.sas.com/sashtml/

They have a list of formats/informats under the SAS language selection/formats

Use the following info to get in:

username: onlinedoc
password: sas

Klaz