DATA Step, Macro, Functions and more

Reading a file with a macro

Reply
N/A
Posts: 0

Reading a file with a macro

I sort of tried before and couldn't.
I'm going to try again.
But, while I'm working on it, maybe someone else has already done this and can help.

I want to access a file within a macro, without use of a data step.
The file has a single line/record/observation in it.
I want to read that single record and assign the value to a macro variable.
Again, without use of a datastep, just essentially pure macro.
N/A
Posts: 0

Re: Reading a file with a macro

at
http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a000210888.htm see the online doc for function fRead(), which can be invoked in the macro environment as well as in a datastep.

PeterC
N/A
Posts: 0

Re: Reading a file with a macro

To do that, I believe the first step is to use the "%sysfunc" macro function.

The example in the SAS documentation is

[pre]
%let filrf=myfile;
%let rc=%sysfunc(filename(filrf, physical-filename));
%let fid=%sysfunc(fopen(&filrf));
%if &fid > 0 %then
%do %while(%sysfunc(fread(&fid)) = 0);
%let rc=%sysfunc(fget(&fid,c,200));
%put &c;
%end;
%let rc=%sysfunc(fclose(&fid));
%let rc=%sysfunc(filename(filrf));

[/pre]

To improve the usefulness of this, it should be prefixed with

[pre]
%local filrf fid rc c ;
[/pre]

This should eliminate any naming collisions.
N/A
Posts: 0

Re: Reading a file with a macro

I understand why the online-doc shows only fragments of solutions.

You might be more help if you show an example of the external file you need to read and the corresponding macro variables that need to be created.

I would be more motivated to help if you explain why it is appropriate to avoid such a simple data step.

PeterC
N/A
Posts: 0

Re: Reading a file with a macro

I have a set of macros wrapped around username and password management, including: %getID and %getPassword(user_id).

I also have macro and include sets for database access.

[pre]
%macro database;
datasource='a_place' provider=sqloledb user='user_id' password='encoded_password' prompt=no
%mend;
[/pre]

The include file "database.sas" contains:

[pre]
libname db_lib oledb %database;
[/pre]

This makes for a single point of maintenance for the database library definition.
It can be used as a SAS LIBNAME through the include file, or within a proc sql pass-through

[pre]
proc sql noprint;

connect to oledb as db (%database);

create table selected as
select * from connection to db
( select ... );

disconnect from db;
quit;
[/pre]

Now, if I wanted to change the %database macro to use the "%getPWD(user)" macro, it cannot inject a SAS dataset to retrieve the password, or this thing fails.

The relevent example here is

[pre]
filename pwfile 'external-filename';

options symbolgen;

data _null_;
infile pwfile obs=1 length=l;
input @;
input @1 line $varying1024. l;
call symput('dbpass',substr(line,1,l));
run;

libname x odbc dsn=SQLServer user=testuser password="&dbpass";
[/pre]

This cannot be used for ' password="%getPassword(user_id)" '
and neither can

[pre]
%let password=%getPassword(user_id);
%if 1=1 %then
... password="&password" ... ;
[/pre]

inside "connect to oledb as db (%database);"

The desire is to maintain maximum security and portability of the code and minimize the maintenance of users and their passwords.

We have AD, and the SAS MetaData Server, whose user's are refreshed every night from AD. But, AD does not, and will not for some time to come, yet, contain application user ID's -- like, for instance, "Oracle". So, if I create the library in the MetaData Server, I have to hard code in the user and password into the libary definition, which defeats the security and maintainability issues.

So, for unattended batch processing, I have created the macros and language structures. Of course, now there is the chore of having to set/change users and passwords manually in two places: 1) AD, 2) for my group's SAS stuff. But since a user has to update their password at least every 30 days, doing this twice isn't so bad, and application level passwords can likewise be easily maintained. Message was edited by: Chuck
N/A
Posts: 0

Re: Reading a file with a macro

My final solution was to not use external files, but a permanent SAS dataset and then to use %sysfunc to wrap around "open" and "fetchobs". I also used "%syscall set ... ".

I got most of this from SAS examples and simply adapted to code to my use.
Ask a Question
Discussion stats
  • 5 replies
  • 221 views
  • 0 likes
  • 1 in conversation