topic Re: Replace in file, universal solution. in SAS Programming

Replace in file, universal solution.

Yura2301 — Tue, 03 Jun 2014 08:56:57 GMT

Hi all,

I would like to create some macro that will replace any string in input file(actually formatted file with sas code) to any other string.

I quickly created small code that ,as I thought initially,will solve the task.

%macro replaceInFile(inFileName,outFileName,from,to);

data _null_;

length str strNew $32767;

infile &inFileName dsd dlm='0D'x lrecl=32767;

input str;

strNew=tranwrd(str,"&from","&to");

file &outFileName lrecl=32767;

put strNew;

run;

%mend replaceInFile;

The macro simply use/in out filenames to read one file and create copy of it with replace strings.

Replace I made usingt tranwrd function, and it works ok - in new file all strings that had to be replaced were actually replaced.

But during further tests I experienced another issues - for some files disappears formtting(start spaces or tabs) and also character ' in some cases also disppear in new file.

In attached file is example of such cases.

If you'll execute that macro for this file - new output file will looks like nigtmare - no formatting, char ' disappears for some expressions etc.

So now I hesitate if it's possible to create some general universal solution for such goals.

Maybe there are some ready smarter approaches for such cases?

Thanks!

Re: Replace in file, universal solution.

RW9 — Tue, 03 Jun 2014 09:14:36 GMT

Hi,

This is just my 2p's worth. If I had to do this, I would probably write a small VBA macro in Word and use the search/replace functionality in there. Should be pretty simple, loop over list of files in directory, then a replace() command. Reason is you will hit some walls within importing the text files to SAS as data and then exporting them whilst retaining formatting. Word, which is built to handle text *should* do a better job of it.

There are also freeware out there which do a similar job: Actual Search and Replace - Tool for Text Files

Re: Replace in file, universal solution.

Yura2301 — Tue, 03 Jun 2014 09:20:40 GMT

Hi Rw9,

Not sure if VBA will work under AIX(on Windows it works for sure).

I also not sure if I'll be able to install that enchanced tool for texts formatting, due security reasons:)

But anyway thanks, will take a look for that text tool that you propose.

Re: Replace in file, universal solution.

RW9 — Tue, 03 Jun 2014 09:25:38 GMT

Ah, sorry just assumed Win. There should be something out there for your OS, found this page at a quick search:

http://www.ibm.com/developerworks/aix/library/au-textedit.html

Could just use the OS commands to search replace.

Re: Replace in file, universal solution.

Yura2301 — Tue, 03 Jun 2014 09:30:44 GMT

Ok, clear.

So I have variant of implementation without SAS, that is nice but I still hope that maybe there is some SAS way, maybe some cool option that I just need to put in my macro and everything will work correctly:)

Thanks!

Re: Replace in file, universal solution.

Kurt_Bremser — Tue, 03 Jun 2014 09:35:12 GMT

Come on, SAS as a streaming text editor? For all things, on a UNIX system???

Do you always hammer in your screws and try to eat soup with a fork?

UNIX was designed as a text-handling tool. There is a host of text manipulation tools like, eg, awk.

Re: Replace in file, universal solution.

Scott_Mitchell — Tue, 03 Jun 2014 09:51:16 GMT

Have a look and see if it does what you are after.

I haven't used it in a while, so it may require some amendments.

Re: Replace in file, universal solution.

jakarman — Tue, 03 Jun 2014 10:00:10 GMT

This search string: "unix command replace text in file" is given 15M hits as it that common in Unix. (inventing the wheel?)

The only exception I can think of is rewriting some dynamic web-parts where this is part of an AJAX uhh ASAX approach

Re: Replace in file, universal solution.

Yura2301 — Tue, 03 Jun 2014 11:10:41 GMT

Hi Kurt,

Company is moving from Windows to AIX, so I'm new in UNIX.

Offcource SAS isn't best tool for seriosu file manipulations but if the goal is more-less simple why not use SAS?

On Windows I used VBS scripts+X command when I need to made some serious code related with file system etc., but when I had to just copy/rename etc. file I think SAS can be used.

I supposed that replace one word to another in file and save previous file formatting is belong to those "easier" part of tasks, but suppsoe I was wrong due all replier here advise rid of SAS for this task.

Anyway thanks.

Re: Replace in file, universal solution.

Yura2301 — Tue, 03 Jun 2014 11:14:50 GMT

Hi Jaap,

As I replied to Kurt - I split tasks related to file manipulations on two parts - simple and complicated, simple task I implement using SAS functions etc., complicated - using X command, bat files, vbs scripts etc.

But innitially I supposed that this concrette task belongs to simper part, du it just replace one word to another in file, but now I'm not sure that I was right, suppose will really need use some unix commands better.

Thanks!

Re: Replace in file, universal solution.

Kurt_Bremser — Tue, 03 Jun 2014 11:24:46 GMT

This works in AIX:

sed -e's/oldttext/newtext/g' oldfile>newfile

The command in the single quotes is what you would do manually in vi

You can of course issue this command from inside SAS with the X statement or the SYSTEM() function or the CALL SYSTEM() routine.

Re: Replace in file, universal solution.

Ksharp — Tue, 03 Jun 2014 12:34:34 GMT

VI editor under UNIX environment.

Re: Replace in file, universal solution.

Yura2301 — Tue, 03 Jun 2014 12:49:59 GMT

Hi Ksharp,

I need to create code that will do it in loop for many files(>100 sas codes), so if VI editor is just similar to N++(maybe with more features) it anyway not ok due I need to apply changes to batch of files.

Anyway thanks!

Re: Replace in file, universal solution.

jakarman — Tue, 03 Jun 2014 12:53:32 GMT

As you are switching from Windows to Unix there are some attention points.

Desktop portable Windows tools, evaluate:

- Terminal usage, yes like the old mainframe days, for the shell access something like putty and being graphical a X-server (moba xterm 9.4)

- Upload/download to Windows, sftp/ftp, winscp and notepad++ with add-ons. (compare/replace)

Data conventions, get accustomed to:

- the LF (Unix) and CR/LF Windows record difference. With binary transfers it could get mixed up in the wrong way.
- Latin1 wlatin1 differences in the encoding (single byte) and the utf8 (multiple byte) Excel Word and Html are already using utf8.
Most people still working/thinking in wlatin1.

I expect you are getting EGuide delivered with the promise nothing will really change to you. (yeah..... forget that).

Support for your SAS usage.
You may be classified as a very dangerous person for the company using VBA and Windows scripting.
They may possible try to lock you out from all of that. You are needing a guy as platform-admin that will really will try to help you.
The first hurdle is the default setting of noxcmd for all SAS services, not allowing you to access Unix.

Re: Replace in file, universal solution.

Yura2301 — Tue, 03 Jun 2014 12:54:07 GMT

Hi Kurt,

yep, after a few tests it looks like this line solve the task, works quick and not demage any spec. character in file.

Thanks!

Re: Replace in file, universal solution.

Yura2301 — Tue, 03 Jun 2014 12:56:09 GMT

Hi Scott,

Your macro looks like has more features, including one that I need, but guys here provide also solutions in one line, so I'll probably will go to study UNIX:)

Thanks!

Re: Replace in file, universal solution.

Yura2301 — Tue, 03 Jun 2014 13:07:54 GMT

Hi again Jaap,

All tasks that I made using VBA scripts wasn't related to business data etc., it was usually one time event tasks for achiving some local goals and couldn't really demage anything, plus offcource I didn't execute any vbs on production:), only development environmant.

For some tasks I also used Perl just becouse it really good in parsing stuff etc.

Thanks!

Re: Replace in file, universal solution.

jakarman — Tue, 03 Jun 2014 13:19:18 GMT

That is nice, you are not a newbie. It looks you have not got locked out, also nice.
For Perl, did you use Prxparse? SAS(R) 9.4 Functions and CALL Routines: Reference, Second Edition
It is Perl string parsing stuff called from SAS

Re: Replace in file, universal solution.

Yura2301 — Tue, 03 Jun 2014 13:26:03 GMT

Hi Jaap,

Yep, I used couple of SAS function that support regular expressions - PRXChange etc.

On big strings it works fuster then SAS functions for working with characters.

Thanks!