topic Data format question in SAS Programming

Data format question

deleted_user — Fri, 24 Jul 2009 07:18:50 GMT

Hi, I am facing a problem when i use SAS 9.2 version.

I have imported some excel 2007 file (.xlsx file) to the SAS lib. The data in the excel 2007 are all in the same format.

When i try to use
DATA ...
SET FILE1 FILE2

It tells me error because of one variable in these two file has different format.

I check them in the SAS lib file. the same variable, say, varible MTM are in different format in the two files.

one is numerical, the other is character.

How come? as they have the same format in excel. After they are imported to SAS, it becomes different. This makes me fail to combine the files. (files have too many rows, which can not combine them in excel....).

If anyone understand what i am asking, would you kindly tell me how can i solve it?

Re: Data format question

Oleg_L — Fri, 24 Jul 2009 07:50:45 GMT

Hi!

You can convert variable MTM to numeric with function input() or to text with function put().

For example;

data file1; set file1;
mtm1=input(mtm,8.); drop mtm; rename mtm1=mtm; run;

then use your code

data ...
set file1 file2

Re: Data format question

deleted_user — Fri, 24 Jul 2009 07:58:45 GMT

Thank you so much.

forget to bring my laptop today.:)

I will back home to try your method later on. I am thinking to convert the format, but i did not know how to. A new user, kinda.

i would tell u whether it works or not.:)

Re: Data format question

Oleg_L — Fri, 24 Jul 2009 08:32:49 GMT

Lucilla,
you should remember about informat using the input function and format using put function.
If you want to convert text variable MTM to numeric and text looks like "111,233,654" then you should use the following informat to function input:
mtm1=input(mtm,comma17.);
See your data before proceeding.

Re: Data format question

deleted_user — Sat, 25 Jul 2009 05:04:23 GMT

too bad....

i cannot even open the file... as it is too big files..

it said "ERROR: (8) Not enough storage is available to process this command. [SASZAF ]
ERROR: (8) Not enough storage is available to process this command. [ViewTabl]
ERROR: Out of memory.
."

I have already clean up my disk. and leave about 10 g for each space... it is still not able to open.

Re: Data format question

deleted_user — Sat, 25 Jul 2009 08:50:40 GMT

> Lucilla,
> you should remember about informat using the input
> function and format using put function.
> If you want to convert text variable MTM to numeric
> and text looks like "111,233,654" then you should use
> the following informat to function input:
> mtm1=input(mtm,comma17.);
> See your data before proceeding.

Hi, forget about the memory problem. let us just focus on the data format first.

i use a small file.:)

i check the MTM's attribute.

it is for one file:

"

length : 8
format: best12
informat: 12

Type: numeric (one the right side as default)

For the other it is

length: 4
format:$4
informat:$4
type: character

Re: Data format question

Doc_Duke — Sun, 26 Jul 2009 14:34:31 GMT

Lucilla,

You may be able to go back to the original Excel sheets and fix the problem there.

Depending on how you brought the data in, SAS looks at the applied Excel format and/or the first rows ( can be changed, I think the default is 50).

If you think that column should really be numeric, look at your excel sheet that is character. Change the format to display as a number. Scan down through the data. Are there garbage characters that could come out. You may need to do some basic data cleaning.

I'm not sure how you are set up, but I am using SAS 9.2 and EGuide 4.2 and the new version of EGuide does a really good job of bringing in data from excel and .csv files; I'm not sure what they changed, but it "feels" more robust.

Doc Muhlbaier
Duke

Re: Data format question

deleted_user — Thu, 30 Jul 2009 03:24:25 GMT

Hi, I use put to

such as :
"data Liutwo.Dealissueyear2006alta;
set Liutwo.Dealissueyear2006alta;
MTM1=put(MTM,12.);
SCORE1=put(SCORE,12.);
DROP MTM SCORE;

run;"

and they are tranformed to Character format, and then i can combine the files together as they are the same format after the transforming.

But if i use input.. it shows error... (Probaly because, there are a lot of missing data coded as "N/A" in excel)

BTW: can i convert the character to numericals after i combine all the files.

> Lucilla,
> you should remember about informat using the input
> function and format using put function.
> If you want to convert text variable MTM to numeric
> and text looks like "111,233,654" then you should use
> the following informat to function input:
> mtm1=input(mtm,comma17.);
> See your data before proceeding.

Re: Data format question

deleted_user — Thu, 30 Jul 2009 03:28:13 GMT

Thanks. Yes, i tried that method.

They are in general format when it is in excel.
When i have changed to numerical in excel, it still does not work. Probaly because they are some "N/A" and some are just numbers.

I sucessfully combined them after using what Oleg_1976 suggested to converting them into the same format using " put". But problems come as later analysis requires numerical format.

What is Eguide? 🙂

> Lucilla,
>
> You may be able to go back to the original Excel
> sheets and fix the problem there.
>
> Depending on how you brought the data in, SAS looks
> at the applied Excel format and/or the first rows
> ( can be changed, I think the default is 50).
>
> If you think that column should really be numeric,
> look at your excel sheet that is character. Change
> the format to display as a number. Scan down through
> the data. Are there garbage characters that could
> come out. You may need to do some basic data
> cleaning.
>
> I'm not sure how you are set up, but I am using SAS
> 9.2 and EGuide 4.2 and the new version of EGuide does
> a really good job of bringing in data from excel and
> .csv files; I'm not sure what they changed, but it
> "feels" more robust.
>
> Doc Muhlbaier
> Duke

Re: Data format question

deleted_user — Sun, 02 Aug 2009 08:48:21 GMT

hi, I solved the problem, with using "put" first.

after importing them all. I then use some thing like.
MTM1=MTM+0;

to convert them all back to numerical format.

Thank you so much for all the way helping me.