External file to external file transformation process logic?

Reply
Frequent Contributor
Posts: 137

External file to external file transformation process logic?

Hello folks,

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.

Many Thanks,

Charlotte from England

Super User
Posts: 5,383

Re: External file to external file transformation process logic?

Sounds like a job for proc import /export which could easily be put in a parameterized macro.

Data never sleeps
Frequent Contributor
Posts: 137

Re: External file to external file transformation process logic?

Would an import proc create a SAS dataset to eventually export to a CSV file, can this be accomplished without creating a SAS dataset?

Super User
Posts: 19,105

Re: External file to external file transformation process logic?

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.

43496 - Convert files created using an ODS destination to native Excel files

Frequent Contributor
Posts: 137

Re: External file to external file transformation process logic?

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.

Thanks,

Charlotte

Super User
Posts: 19,105

Re: External file to external file transformation process logic?

What are the different types that you expect to export, i.e. CSV, TSV, XML???

Super User
Super User
Posts: 6,844

Re: External file to external file transformation process logic?

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?

Super User
Posts: 19,105

Re: External file to external file transformation process logic?

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.

Frequent Contributor
Posts: 137

Re: External file to external file transformation process logic?

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:smileyblushSmiley Happy

Tom, is this what you meant or please correct me:

/* Updating external files*/

data _null_;

  infile file-specification-1;

  file file-specification-2;

  input;

  put _infile_;

run;

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*/

Valued Guide
Posts: 3,208

Re: External file to external file transformation process logic?

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.   

---->-- ja karman --<-----
Super User
Posts: 9,867

Re: External file to external file transformation process logic?

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.

Xia Keshan

Ask a Question
Discussion stats
  • 10 replies
  • 319 views
  • 8 likes
  • 6 in conversation