BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
5 REPLIES 5
deleted_user
Not applicable
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
deleted_user
Not applicable
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.
deleted_user
Not applicable
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
deleted_user
Not applicable
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
deleted_user
Not applicable
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.

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
  • 5 replies
  • 1897 views
  • 0 likes
  • 1 in conversation