- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I'm in a RHEL environment (migrating from 6.9 to 8.1) running SAS 9.4m7. I've been struggling with a specific application of the FWRITE() function, attempting to overwrite an existing record of a file.
Here's a simplified example:
%macro overlay ;
%local rc ;
%let rc = %sysfunc(filename(homesas,/home/sas/testfwrite.txt)) ;
%let fid1=%sysfunc(fopen(homesas, O, 0, P)) ;
/* create header record */
%let rc=%sysfunc(fput(&fid1, %sysfunc(putc(%str(The Three Stooges), $, 41)))) ;
%let rc=%sysfunc(fwrite(&fid1)) ;
/* The Original Act */
%let rc=%sysfunc(fput(&fid1, %sysfunc(putc(%str(Shemp Larry and Moe), $, 19)))) ;
%let rc=%sysfunc(fwrite(&fid1)) ;
/* In 1932, Shemp left and Curly joins */
%let rc=%sysfunc(fput(&fid1, %sysfunc(putc(%str(Curly), $, 5)))) ;
%let rc=%sysfunc(fwrite(&fid1,+)) ;
/* Shemp returned to replace Curly in 1946 */
%let rc=%sysfunc(fput(&fid1, %sysfunc(putc(%str(Shemp), $, 5)))) ;
%let rc=%sysfunc(fwrite(&fid1, +)) ;
%let rc=%sysfunc(fclose(&fid1)) ;
%mend overlay ;
%overlay ;
When I execute a standard cat command on the file, I get:
> cat /home/sas/testfwrite.txt
The Three Stooges
Shemp Larry and Moe
But when I run cat -e to show hidden characters:
> cat -e /home/sas/testfwrite.txt
The Three Stooges$
Shemp Larry and Moe^MCurly^MShemp$
The expected output (after processing overlays) where "Shemp" at the beginning was replaced with "Curly" and then "Curly" was replaced with "Shemp", should look like this:
The Three Stooges
Shemp Larry and Moe
I've tried different file opening modes:
- Standard mode: %let fid1=%sysfunc(fopen(homesas, O, 0, P)) ;
- Binary mode: %let fid1=%sysfunc(fopen(homesas, O, 0, B)) ;
- No record structure: %let fid1=%sysfunc(fopen(homesas, O, 0, P RECFM=N)) ;
None of these approaches resulted in the overlays being processed as expected. The carriage returns (^M) are being written to the file, but they're not causing the text to be overwritten when the file is read.
The question is: How can I effectively overlay a line of a file using the FWRITE() function so that when the file is read later, the overlays are processed correctly?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Sorry everyone, a couple of edits to the code above:
Use this for the top part:
%local filref rc fid1 ;
%let filref = homesas ;
/* assign filename */
%let rc = %sysfunc(filename(filref, /home/sas/testfwrite.txt)) ;
/* open file */
%let fid1=%sysfunc(fopen(&fileref, O, 0, P)) ;
/* create header record */
%let rc=%sysfunc(fput(&fid1, %sysfunc(putc(%str(The Three Stooges), $, 41)))) ;
%let rc=%sysfunc(fwrite(&fid1)) ;
And then release the filename at the bottom:
%let rc=%sysfunc(filename(&filref)) ;
I hope someone can explain to me the wisdom behind using a macro variable in the FILENAME function for the first argument that does not have an & in it... but that's another discussion thread.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
FILENAME(fileref, filename <,device-type> <, 'host-options'><, directory-reference>)
Required Argument
fileref
specifies the fileref to assign to the external file. In a DATA step, fileref can be a character expression, a string enclosed in single quotation marks that specifies the fileref, or a DATA step variable whose value contains the fileref. In a macro (for example, in the %SYSFUNC function), fileref is the name of a macro variable (without an ampersand) whose value contains the fileref to assign to the external file. If the function is used within a DATA step, the fileref must be enclosed in single quotation marks. If the function is used in macro code, the fileref must not be enclosed in quotation marks.
Requirement If fileref is a DATA step variable, its length must be no longer than eight characters. Tip If a fileref is a DATA step character variable with a blank value and a maximum length of eight characters, or if a macro variable named in fileref has a null value, then a fileref is generated and assigned to the character variable or macro variable, respectively.
When a function can change the value of an argument it means the value was passed by address (instead of by value on a stack). This is the only way the function can change (assign) something in the calling scope. This means the fileref value is obtained by derefencing the passed address in order to get the value. If at that time the value is blank the function internally computes a new unique fileref value for the SAS session and stores it at the address and then upon return the value is available to the caller. (variable name in DATA Step and &<symbolname> in macro)
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Most likely the extra bytes are the macro quoting you applied.
%str(The Three Stooges)
Why are you using macro code to manipulate DATA? Why not use actual SAS code instead?
PS The FILENAME() function wants the NAME of the variable to use to find the FILEREF name to use. When the variable is empty (all blanks) it makes up a name for you and stores it into the variable.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
The "why" is a bit complex. I'm needing to manipulate file content based on conditions entered inside the double-quotes of a SAS statement, which screams "macro function" to me. I can't go into much more detail due to confidentiality, but here's a broader application that is similar:
title1 "Creating report on %titlecard(REPORTNAME)" ;
Not a real practical application either, I admit--but I'm trying to give a more operationally-friendly example. Suppose you want to know how many times different users run a report based on the title statement (yeah why would you want that but I'm scraping to give an example for public use). When the macro TITLECARD is called, it would return a string for the given value of REPORTNAME, but also append records to a master file showing detail of who has requested which reports and when.
It's gotta work inside double quotes. That's the why.
I'll run some tests with different quoting options. I don't need to use macro quoting options in the three stooges example, but I do in the actual applciation where semicoons and double-quotes get invovled.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
DOSUBL() to the rescue.
So something like this:
%macro titlecard(report);
%local rc;
/* Add record to text file */
%let rc=%sysfunc(dosubl(%nrstr(
data _null_;
file 'reportlist.txt' dsd mod ;
datetime=datetime();
report=symget('report');
user=symget('sysusername');
put datetime :datetime26.6 user report ;
run;
/* Return REPORT as result of macro call */
&report.
)));
%mend ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Well, I don't see any overwriting in that example...
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
That is becuase I could not figure out what your original macro was even TRYING to do. Instead I showed how to run actual SAS code from a macro that does not emit any code.
If you want help with modifying a text file in place look at the sharebuffers options of FILE/INFILE statements. https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsref/n1rill4udj0tfun1fvce3j401plo.htm#n...
If you want help with modifying a dataset look at MODIFY statement or perhaps the UPDATE statement of PROC SQL.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I appreciate you looking into it.
PROC SQL won't do it for me because ... well... that's a procedure, and I'm not sure it can modify text files.
As I review the FILE/INFILE statements, it appears that they only apply for use inside a DATA STEP, which is also outside the requirements. There may be useful information there that can apply to the FWRITE() function.
What I'm trying to do is simple enough operationally: Using the macro facility or some other tool outside the data step and procedures, write a record to a text file and then overlay that same record with different data on that same text file. The solution must work in SAS, must work on any operating system, and must function in open code--meaning not interrupting a procedure or data step.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I was able to combine the DOSBUL() function to build two data steps and use the second to overwrite content on the second line. For the example, I removed the detail of Shemp coming back to make the output clear. The result is both cat /home/sas/testfwrite2.txt and cat -e /home/sas/testfwrite2.txt appear the same.
%macro overlay ;
%local rc ;
%let file = /home/sas/testfwrite2.txt ;
%let rc = %sysfunc(dosubl(%str(data _null_ ;file "&file" ;put @1 'The Three Stoges' ;put @1 'Shemp Larry and Moe' ;run ;))) ;
/* In 1932, Shemp left and Curly joins */
%let rc = %sysfunc(dosubl(%str(data _null_ ;infile "&file" sharebuffers ;file "&file" ; input ; if _n_ = 2 then put @1 'Curly' ;run ;))) ;
%mend overlay ;
%overlay ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
FWRITE() is a SAS function. To call it in a macro you need to wrap it in %SYSFUNC() macro calls. But that will have trouble with any text that contains commas or other characters that are important to the macro language. You added macro quoting to protect them. But those extra characters that macro code uses to indicate the macro quoting are now getting written into the file.
So you should use just skip the macro code for calling FWRITE(). Instead call it in a DATA step just like you would call the any other SAS function. If you want to run the data step in the middle of macro call then use %SYSFUNC() to call DOSUBL() so that the data step runs in a "side session".
But I am not sure why you want to use FWRITE(). That was a function originally written for SCL code. But you don't need to write any SCL code. So you need to use a data step to call it. But if you are running data step then it is much much easier to just use the normal INPUT and PUT statements to read and write from a file.
For detailed help share an example of the source file before you want to change.
options parmcards=in;
filename in temp;
parmcards;
This is a file with
two lines
;
Explain what changes you want to make.
???
And share the expected result after the changes.
options parmcards=in;
filename in temp;
parmcards;
This is a modified file with
two lines
;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Perhaps I misunderstood what you are trying to do?
The optional second argument on the FWRITE() function is basically what we used to call Fortran Carriage Control characters.
In that world the first character on the line controlled how the carriage on the typewriter would move.
A space meant normal single spacing.
A 0 meant double spacing.
A 1 meant issue a Form Feed (do load a new piece of paper)
And the + that your code is using meant to overstrike the previous line.
To implement those in a normal text file it mean that for when you write a line of text you do not write the end-of-line characters (which is Windows is Carriage Return and then Line Feed). Instead when your first character is a space you write the endo of line and then write the new line. And when your first character is + you just write the Carriage Return (so the typewriter does not move to a new line) so that when the next line prints the new characters over the others. Doing that to a terminal does not really have the same result (unless you are using an ancient cathode ray tube for a monitor and it has very long retention)
So let's try making those same FWRITE() calls in a data step and look at the contents of the file we get.
filename myfile temp ;
data _null_;
fileref='myfile';
fid=fopen(fileref,'O',0,'P');
rc=fput(fid,'The Three Stooges');
rc=fwrite(fid);
rc=fput(fid,'Shemp Larry and Moe');
rc=fwrite(fid);
rc=fput(fid,'Curly');
rc=fwrite(fid,'+');
rc=fput(fid,'Shemp');
rc=fwrite(fid,'+');
rc=fclose(fid);
run;
data _null_;
infile myfile recfm=f lrecl=80;
input;
list;
run;
Result
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6 1 CHAR The Three Stooges..Shemp Larry and Moe.Curly.Shemp.. 52 ZONE 5662567662576666700566672467772666246604776705666700 NUMR 485048255034FF753DA385D00C122901E40DF5D352C9D385D0DA NOTE: 1 record was read from the infile MYFILE.
So you see before the second line it wrote the CR and LF. But before the next two it only wrote the CR. And apparently closing the file added the last CR and LF.
You could more easily generate the same thing using PUT statement. If you use RECFM=N then you can decide when to write end of line characters. This step makes the exact same file.
filename myfile temp ;
data _null_;
file myfile recfm=n;
put 'The Three Stooges';
put '0D0A'x 'Shemp Larry and Moe';
put '0D'x 'Curly';
put '0D'x 'Shemp';
put '0D0A'x;
run;
I am still interested in know what you are actually trying to do here. Do you just want to append text to a file? Do you really want to make a file that tries to overstrike ink onto paper?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
The good news is that for my specific requirements, I was able to get the DOSUBL() function to work. I created a big ol' macro with about 20 lines of SAS code, then put an OPTIONS NONOTES; and OPTIONS NOTES; on the top and bottom of it. Like most problems I encounter in SAS, the solution often lies in selecting a different approach. And if I hadn't gotten your input, I'd still be working on it now. Thanks are in order!
I think I can explain a little more on what is needed. Don't worry that you didn't understand it because I really struggled figuring out how to explain it... but not really "explain" it!
The purpose is to create a macro function that will validate connection to a database that management requires an extra level of security. The function will return a 1 if the connection is validated and a 0 if it is not validated. Usage will be something like this:
%IF %VALIDATE_DB = 1 %THEN %DO ;
%PUT run process ;
%END ;
%ELSE PUT DATABASE NOT VALIDATED ;
Yes, I know I can do all this in a data step and use procs. And yes, I know that would be easier. And yes, I know I can assing the validated value to a global macro variable. And oh yes, I also know that maybe management should let certain users of a database actually know the password and other key secure tools. But that's not the requirements, and I can't change them. The requirements are to create a macro function that will return a 1 if the database is validated up and a 0 if it is not validated up AND the user must be blind to the logic behind it. The validation must be done in SAS, so writing a different utility to check that the databse is up or down just won't do, either. SAS has to hit the database and pull at least one record from one variable from one table.
Let me see if I can generalize it but still be specific enough. Here's a similar solution to the DOSUBL() method that I got to work inside the macro:
First, I created the rather lengthy macro SASCODE with... well... the SAS COde in it with the key parameters and embedded site macro to verify the connection authority:
%let sascode = %str(
OPTIONS NONOTES ;
DATA _NULL_ ;
FILE "pgmfile.txt" ;
PUT "DATA TST ;" ;
PUT "super-secret-code-I-cannot-tell-you with embedded macros and macro variables" ;
PUT "some that can resolve at the creation step and some that must not until this program is run" ;
PUT "so macro quoting and resolution became a beast. I had been attempting to overstrike and build" ;
PUT "this program as a file backwards to allow certain tokens to resolve and other tokens to not resolve" ;
PUT "it was painful" ;
PUT "RUN ;"
PUT "DATA _NULL_ ;" ;
PUT "SET T1 ;" ;
PUT 'FILE "testfile.txt" ;' ;
PUT 'IF _N_ = 1 THEN PUT "UP" ;'
PUT 'RUN ;' ;
OPTIONS NOTES ;
) ;
%LET RC=%SYSFUNC(DOSUBL(%SUPERQ(SASCODE))) ;
I then build up a command for %SYSCMD to sumbit and run an independent SAS session with no log and no printed output that submits the code generated above. When that is done, the file TESTFILE.TXT is ether a null file or a file of one record containing the two characters "UP". Then inside the same macro, I can just check the contents of that file easily, like this:
%let fid1=%sysfunc(fopen(&txtfname)) ;
%if &fid1 %then %do ;
%let rc = %sysfunc(fread(&fid1)) ;
%let rc = %sysfunc(fget(&fid1, textline, 8)) ;
%let rc = %sysfunc(fclose(&fid1)) ;
%end ;
%if %str(&textline) = %str(UP) %then %let status = 1 ;
%else %let status = 0 ;
I can then throw the value of &STATUS back to the program. That runs something like htis:
%MACRO VALDB ;
%local textline status (and a lot more) ;
/* logic as discussed above */
%if %str(&textline) = %str(UP) %then %let status = 1 ;
%else %let status = 0 ;
&status
%MEND VALDB ;
%PUT Database Status is %VALDB ;
When executed, the SAS log will show this:
Database status is 1
OR
Database status is 0
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
So this has gone in a totally different direction that what I expected.
First let me say I reject the idea that you need a macro that you can call in the middle of statement (ie works like a function) for this situation. It is just as easy for the users to have macro populate a macro variable that the user's code can then test. Something like this:
%CHECK_DB_STATUS()
%IF &DB_STATUS = 1 %THEN %DO ;
%PUT run process ;
%END ;
%ELSE %do;
%PUT DATABASE NOT VALIDATED ;
%end;
As to the question of how to connect and test the database without displaying your credentials that is a different issue and there are many solutions.
For example SAS actually has tools for managing connections. Perhaps you could just use those?
If you need to pass a password to connect then the question is how to provide SAS that password. You could store the password in a file and then use SAS code to retrieve it. Or put it in an environment variable.
For systems where each user needs their own password to access then have the user make a text file that only their account can read. Then even if the SAS code is shown it does not help anyone else gain access to the database since they will not be able to read the text file that has the hidden information.
And for a small extra level of protection you can use PROC PWENCODE to generate a plain text encrypted version of the password and use that instead of the actual password.
And if the goal is just to confirm the database exist (instead of actually establishing a connection that will be used for actual work) then use an account that only has enough permissions to perform that test so that the risk if its credentials are discovered is limited to the ability to detect if the database exists.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You got my vote, but ... the requirements call for what the requirements call.