nummeric to text but at the number appear

[Samero]

Hello everyone

I have a set of files (more than 60 files) i want to merge the in one table using set ...... ...... etc

but the problem are in two variables which are saved as character in some files and as number in other files

actually these files are recieved as .csv files and you have to import them in Excel using Data>>>from text and then do the usual steps when exporting to excel and choosing the right format for the variabels...

but that was not done correctly for all the files

and now when i want to combine them in one file it doesnt work

I tried to use this code:

MKGchar = put(MKG, f10. -L);
drop MKG;
rename MKGchar=MKG

it does work but the problem is I have a variable which is a long number; in excel it appears like 2,76+14  (because the format is standard) and you can change it to text using "Data >>> text to column" and then the right long number will appear 276001303712181 but when i use this code in sas the variable does converted to text but the number does not come any more even when i export it to excel again.

that can be done in excel but takes long time because i have  alot of files and to do this for every file is a very long and hard job

is there any way to do this using SAS? (to convert this variable to text and at the same time the right number appear)

[ChrisNZ]

1. So does the CSV file contain the value 2,76+14  or 276001303712181 ?

2. > but the problem are in two variables which are saved as character in some files and as number in other files

Fix that problem first. Why is this happening? Use a data step to read the CSV files. 

3. > MKGchar = put(MKG, f10. -L);
Reading a variable that's 14 in length with a 10-character buffer will never work will it?

4. > does not come any more even when i export it to excel again.

SAS always exports correctly. This is an Excel display issue. How do you export?

[Samero]

1- it contains 276001303712181 but appers 2,76+14

2- Use a data step to read the CSV files. (they are too many files)

3- How do you export? I export using SAS (export SAS Table)

[ballardw]

---

@Samero wrote:

actually these files are recieved as .csv files and you have to import them in Excel using Data>>>from text and then do the usual steps when exporting to excel and choosing the right format for the variabels...

---

Why do you think that step of using Excel is needed?

With CSV of a common layout the approach is generally to write a data step to read the values into common named variables with the same properties. Once you get a single CSV read correctly then you replace the source filename and the output data set name. Or even read multiple CSV files at one step.

If you are importing from Excel, xlsx, files, then you NEVER have any guarantee of the variable types, lengths or properties because Proc Import guesses for each and every file.

One approach might be to use Proc Import directly on one of the files. The procedure will generate data step code that you can copy from the Log and paste into the editor. Clean it up. The Informat statements can be adjusted to change variable type for any of the variables that should be numeric but came in as character. Check numeric variables that should be character like ID values, account numbers, part numbers, etc. that might contain leading zeroes and read as character.

You may also want to consider making the character variables a little longer to allow for changing value lengths like people's names.

Once you have the code working, you save and then change in the infile to reflect the new CSV file and change the output data set name as desired.

MUCH easier than any stupid "import into Excel" and reusable.