You might be surprised at the amount of management support might appear when $$$$ are pointed out.įor what it may be worth: changing the informat property for a variable after it has been imported is basically lying to yourself and others. Talk to your supervisor about the total cost in man hours and resources the current data process involving Excel will entail as well as delays in response to questions based on this daily data from having to "fix" things daily. Extrapolate that work load over a 4 or 6 month period. If the data comes from a database it is almost certain that data has consistent data properties.ĭocument how much time it takes to adjust everything for ONE day of data. Perhaps you might be better off with a SAS/ACCESS and direct connections to the data source. An agreed upon file format for each of these files, a data step to read each and the daily requirement becomes changing input file names and output data set names (if needed).Īnother option to consider if this data is being created by a different data base is that SAS provides a number of ways to connect directly to many databases. Even if some users of this data want the spreadsheet format it may well be possible to have interchange data provided in another form for you, especially if these files are created by another program (especially a data base).
The process might be creating Excel files because "every one wants Excel" is a default thought pattern for some. You might discuss with whoever is creating these files if another file creation option such as CSV (or other delimited text file format) would be practical. &Name1 outfile= "I:\Dat\3950MFI\OUT\DER_&LastMonth.xlsx" * Inserts all datasets from above into the first dataset from the Library */ Proc import datafile= "&path\Control\&Year\&LastMonth\&File&i" * Imports all workbooks specified in variable List from table FilesInFolder and saves them as datasets in Library DER */ If final then call symputx(trim( 'Total' ), _N_) * Creating global macro variables */ /* not local */Ĭall symput(cats( 'File', _N_), trim(File)) Ĭall symput(cats( 'Name', _N_), trim(nliteral(substr(File, 1 ,min( 32, length(File)- 4 ))))) * Ascending order for varibale File in table FilesInFolder */
* Making a list of all excel workbooks into table FilesInFolder */ Options validmemname=extend /* to allow non-standard dataset names */ * Find and import relevant excel workbooks into SAS */įilename folder "&path\Control\&Year\&LastMonth" %Let LastMonth = %sysfunc (putn( %sysfunc (intnx(month, %sysfunc (today()),-1, same)),monname.)) NOTE: Table DER.'20180302.'n has been modified, with 19 columns.ĮRROR: Value 3 on the SELECT clause does not match the data type of the corresponding columnĮRROR: Value 9 on the SELECT clause does not match the data type of the corresponding columnĮRROR: Value 11 on the SELECT clause does not match the data type of the corresponding column data set was successfully created.ĮRROR: Character column ID_2 requires a character format specification.ĮRROR: Character column ID_2 requires a character informat specification. NOTE: The import data set has 0 observations and 19 variables. NOTE: PROCEDURE SQL used (Total process time): NOTE: Table DER.'20180301.'n has been modified, with 19 columns. NOTE: PROCEDURE IMPORT used (Total process time): NOTE: The import data set has 3 observations and 19 variables. I have tried to modify my variables into both numeric/character with no luck. My issue arises when SAS starts to import the first file with no data because some files are empty. It converts my numeric variables into character. I want to import multiple Excel files (approx 20 daily files) into SAS and merge them into 1 SAS file.
0 kommentar(er)
