BookmarkSubscribeRSS Feed
CharlotteCain
Quartz | Level 8

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

10 REPLIES 10
LinusH
Tourmaline | Level 20

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

Data never sleeps
CharlotteCain
Quartz | Level 8

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

Reeza
Super User

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

CharlotteCain
Quartz | Level 8

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

Reeza
Super User

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

Tom
Super User Tom
Super User

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?

Reeza
Super User

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.

CharlotteCain
Quartz | Level 8

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

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

jakarman
Barite | Level 11

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 --<-----
Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 800 views
  • 8 likes
  • 6 in conversation