Converting from character to Numeric

[127]

I have hundreds of variables like age, type, etc. that are not in particular order and have different lengths. I need to be converted thme from character into numeric variables (this file was exported from a web-bases system into excel, which then i am importing the excel into SAS). I don't want to use the input and drop statements because that would be a labor intensive. Any ideas for code I can use? 

thanks! 

[Reeza]

Can you export the file as a CSV instead and import that? Do you have/can you make a specification document that indicates what every variable should be in terms of type and format? You have more control over your import this way. 

You will still have to verify each field individually, but data quality is pretty much the most important step. The usual method for me is to use PROC IMPORT with a CSV and then copy the code from the log and manually modify it. 

---

@127 wrote:

I have hundreds of variables like age, type, etc. that are not in particular order and have different lengths. I need to be converted thme from character into numeric variables (this file was exported from a web-bases system into excel, which then i am importing the excel into SAS). I don't want to use the input and drop statements because that would be a labor intensive. Any ideas for code I can use? 

thanks! 

---

[127]

I can only import the data into excel, even after I save the excel file as CSV and import into SAS the formats sill remain the same (character).

[Reeza]

Then you imported the CSV file incorrectly. Did you use a data step or the copy/paste method I mentioned?

PROC IMPORT guesses and it often guesses incorrectly. If it's one time job you can set GUESSINGROWS=MAX and see if it works but if this is for any sort of repeatable process that is not recommended.

---

@127 wrote:

I can only import the data into excel, even after I save the excel file as CSV and import into SAS the formats sill remain the same (character).

---

[SuryaKiran]

Can you please provide more information. Show some sample data and output you like.

What does this mean?

 I don't want to use the input and drop statements because that would be a labor intensive

[127]

Data example in Excel that's currently in character 

ID	EnSur	Grp	Age	Bar	Test	Date
1	2	3	9	12	100	1/15/2018
2	1	0	8	2	80	2/6/2018
3	2	1	9	11	95	6/4/2017

I want the same variable names displaying the same values but they should be numeric: 

ID	EnSur	Grp	Age	Bar	Test	Date
1	2	3	9	12	100	1/15/2018
2	1	0	8	2	80	2/6/2018
3	2	1	9	11	95	6/4/2017

I also need to do this import quarterly. I can only download the data from the web-based system into excel. 

[Reeza]

---

@127 wrote:

I also need to do this import quarterly. I can only download the data from the web-based system into excel. 

---

Sigh, that's a problem. So you will then have to manually ensure every variable meets the type you need because you cannot guarantee that in an Excel file. And I do mean every variable even if it's not problematic in this data set because it could be in future data sets. Excel does not have the concept of types in columns similar to databases and that leads to these types of issues. 

[127]

that's what I wondered but I thought that maybe someone had a better answer. I appreciate your time looking into this. 

[Reeza]

Convert to CSV as a step?

VBS script to do that.

'Set the parameters of the program

'Original extension of files - note this is case sensitive
oldExt = "xlsx"

'New extension desired
newExt = "csv"

'code for new extension
newExtCode = 6

'common codes include:
'xlsx=51
'xls=-4143
'csv=6
'xml=46

'path to folders with files to convert
path2File = "C:\_LOCALdata\temp\Temp\"


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Do not change below this line
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

set xlapp = CreateObject("Excel.Application")
set fso = CreateObject("scripting.filesystemobject")
set myfolder = fso.GetFolder(path2File)
set myfiles = myfolder.Files
xlapp.DisplayAlerts = False
 
for each f in myfiles
  ExtName = fso.GetExtensionName(f)
  Filename= fso.GetBaseName(f)
    if ExtName=oldExt then
           set mybook = xlapp.Workbooks.Open(f.Path)
           xlapp.Visible = false
           mybook.SaveAs path2File & Filename & "." & newExt, newExtCode
    End If
  Next
  mybook.Close
  xlapp.DisplayAlerts = False
 xlapp.Quit
 Set xlapp = Nothing
 strScript = Wscript.ScriptFullName

[SuryaKiran]

Did you check will PROC HTTP work for you. Instead of creating a excel file and then importing it into SAS, you can directly read the data from web using PROC HTTP.

https://blogs.sas.com/content/sasdummy/2017/12/04/scrape-web-page-data/

[127]

I did not check and I am not sure that's possible. The data are stored in web-based application that's custom built for us and it's password protected.