12-11-2014 11:58 AM
I request your help in providing me with a reusable SAS code in converting an external file, an xls file to a Comma Separated file. The complexity is that I have over 80 odd xls files that needs to be transformed to a CSV file. Can this be efficiently achieved in using datastep/proc logic?
Secondly, if you do have a solution for the above, can that be replicated for use in other transformations, say for example xls to tab and other kinds of delimited files such as tab by making small changes.
Any example would be wonderful for my clear understanding. I'd sincerely appreciate this priceless help.
Charlotte from England
12-11-2014 11:21 PM
Do you need to use SAS at all? Are the files all located in a single folder?
If so this VBA code could be modified to batch process all relatively painlessly. In fact, I'm pretty sure someone else asked this question a while back and the modified code is on here somewhere.
12-11-2014 11:34 PM
Hi Reeza, Very good point. It's just that at my office the entire IT environment has been in the process of moving to SAS, creating a SAS environement so to speak, so I envisaged the thought or an idea of doing it with SAS. Also, my company has bought the whole range of suite of SAS products for whatever, and on the contrary we don't use them all.
I completely agree with you. Yes, a set of files is an a folder and the other set is in an other and likewise many more. If you happen to have the time and by any chance if you come across the thread that has the modified vba code, please share it. That would really mean a lot to me.
12-12-2014 10:42 PM
But since you are using SAS why not create a SAS dataset? Now if you are converting from one text file format to another there is no need to create a stored dataset since you could possibly do it in a data _null_ step. But if want to work with XLS or other non text formats then use the tools that you have to read those formats. So you could use PROC IMPORT or the EXCEL libname engine to convert the XLS to a SAS dataset and then write it back out in what ever format you want.
You could also use SAS to write an visual basic script that could then be run in Excel to make the transformations, but why not just write it and run it in Excel?
12-13-2014 12:06 AM
One possible reason - sometimes when you import things from excel to SAS they get imported incorrectly and then you export them incorrectly. If SAS writes the VBA script then the CSV saved from Excel is less likely to have this problem.
I'd be curious to know why they need to be converted in the first place.
12-13-2014 01:04 AM
Hi Reeza/Tom, Thank you for your time and for your valuable responses. Why need to be converted? Honestly, I myself do not know why we are assigned the task by our boss in the first place and to recommend what is best practice, I do not have the competency nor I am wise enough to have the confidence to back and suggest what would be the best solution. Well, I guess that's perhaps why I am here communities shamelessly bothering you folks.(my apologies:smileyblush
Tom, is this what you meant or please correct me:
/* Updating external files*/
Would it have to be lot of repetitions considering I have over 80 odd files?
/*SAS writes the VBA script then the CSV saved from Excel is less likely to have this problem*/- This is super,and is something I never ever imagined such a functionality and have no clue about that*/
12-13-2014 07:25 AM
The infile/file specifications is your file-definition. When all excel files have the same structure you can use wildcards and retrieving the physical naming before writing out to the new one.
When the structure of all files are different is will become possible more complicated, to think about a generic approach.
Reading xlsx could be by a libname statement. SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition the sheets are becoming tables.
An import procedure should give a good start. SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition
Automating this could be by done by macrotizing when the separate steps are working but no functionality for all exists.
12-13-2014 11:26 PM
I follow Reeza 's suggestion. If you just need convert XLS into CVS , why not use VBScript? Search it at Google, you will get a bunch of VBScript code about converting XLS into CSV file . some like : ExcelToCSV.exe
If you really need SAS to do this kind of job .
proc import + proc export is good.