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!
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!
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).
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).
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
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.
@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.
that's what I wondered but I thought that maybe someone had a better answer. I appreciate your time looking into this.
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
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/
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.
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.