DATA Step, Macro, Functions and more

Opening and closing datasets with %sysfunc in a macro

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 98
Accepted Solution

Opening and closing datasets with %sysfunc in a macro

I am writing a macro, within which it is important for me to calculate the number of variables in one of the datasets created by the macro.

Doing so is straightforward. For example, I can use the following code:

      varcount=%sysfunc(attrn(%sysfunc(open(modout,i)),nvars));

The problem I run into with this code is that it leaves the dataset "modout" open, which produces errors when you try to run the macro again in the same session.

I can get around that by modifying the code as follows:

     %let did=%sysfunc(open(modout,i));

     %let varcount=%sysfunc(attrn(&did,nvars));

     %let qid=%sysfunc(close(&did));

Now, this works exactly as intended. However, it feels to me like a bit of a clunky workaround. It seems inefficient to have to specify and assign values to three macro variables where before I was only using one. However, I cannot find a way for the close function to work with my first chunk of code. Since it seems that the close function only works when input the numeric dataset id output by the open function, I can't find a way of keeping this code simple. My main concern is that it is ungainly to have a lot of extra macro variables floating around in my code, and it gets even worse if I want to do something similar to this multiple times within the same macro, because every time I need to define three macro variables.

So, my questions are:

1) Is there a way to use the close function with my first line of code, that still closes the "modout" dataset but doesn't necessitate having to define 3 separate macro variables in the process?

2) Is there another way to force SAS to close that dataset without invoking the close function? Especially in the case that I might have multiple datasets accessed using the "open" function within the same macro, is there some "universal" close command I could issue that would close all of them at once without having to write separate commands for each one?

3) Especially in the case that there are no suitable answers to 1) and 2), is there are more parsimonious way of counting the number of variables in a dataset than the one I am using?

Thanks in advance for any advice you can give me on this.


Accepted Solutions
Solution
‎06-02-2015 10:55 AM
Super User
Super User
Posts: 7,392

Re: Opening and closing datasets with %sysfunc in a macro

Hi,

With your way of doing it you do need to open the file, get the info, then close it again.

Straight coding, no need for macro:

proc sql;

     select     count(distinct NAME)

     into          :COL_COUNT

     from         DICTIONARY.COLUMNS

     where     LIBNAME="SASHELP"

           and     MEMNAME="CLASS";

quit;

%put &COL_COUNT.;

More importantly though, why do you not know how many columns there will be?  Even with a proc transpose you can pre-calc that.

View solution in original post


All Replies
Solution
‎06-02-2015 10:55 AM
Super User
Super User
Posts: 7,392

Re: Opening and closing datasets with %sysfunc in a macro

Hi,

With your way of doing it you do need to open the file, get the info, then close it again.

Straight coding, no need for macro:

proc sql;

     select     count(distinct NAME)

     into          :COL_COUNT

     from         DICTIONARY.COLUMNS

     where     LIBNAME="SASHELP"

           and     MEMNAME="CLASS";

quit;

%put &COL_COUNT.;

More importantly though, why do you not know how many columns there will be?  Even with a proc transpose you can pre-calc that.

Super User
Posts: 6,928

Re: Opening and closing datasets with %sysfunc in a macro

Do the whole operation (open, attrn, close) in a data _null_ step and use call symput to create the macro variable. Also makes for more readable code, IMO.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 3,777

Re: Opening and closing datasets with %sysfunc in a macro

You don't really need QID you could reuse DID.

But lets back up.  Why do you need to know the number of variables?  How is use used?  Depending on how it's used you may be able to let SAS figure it for you.  Forget about open and close and tell use what "number of variables" doing for you.

Frequent Contributor
Posts: 98

Re: Opening and closing datasets with %sysfunc in a macro

Thank you for the quick replies everyone! (I am new to the community here, so I didn't realize I could only give out a limited number of 'helpful responses'. I meant to give one to all of you. Apologies).

The macro is computing the likelihood ratio test for nested Cox proportional hazards models. Counting the number of variables in the PROC PHREG output (from the outest statement) was simply my ad-hoc method of automatically computing the degrees of freedom for the test, so it doesn't have to be pre-specified (Note: I realize the output from the outest statement has more variables in it than just the model predictors; however, these elements are always shared, so they cancel out when computing the degrees of freedom, which in the case I am dealing with will just be the difference in the number of predictors for the two models).

I realize there may be better ways of doing that, this was purely a quick ad-hoc solution until I could find something more principled. The main reason I didn't mention this in the OP is that my question was mostly academic with respect to the "open" and "close" functions; there are other contexts I also use those functions in, and was hoping for a better method, completely distinct from the variable counting example I used in the question. That is, I am more interested in the question in an abstract sense than practically with respect to my specific programming goal with this macro.

@RW9:  Thank you for that suggestion. I never considered using PROC SQL or PROC TRANSPOSE. It still seems to me that using macro functions is more efficient than calling a PROC, but I guess you are right that opening, accessing, and closing the file again is a waste of computational resources.

@KurtBremser: I suppose it is a matter of preference, but I find the data _null_ step a big ugly to look out, and think the macro functions are clearer to understand. I think that might come from me having a stronger background in R than SAS, and thus am more comfortable with that type of syntax. But I will try the DATA step as well.

@data_null_;: Good point vis a vis QID and DID. I always intuitively assign a new name when I am using a new command, but you are right that I can simply reassign the name I'm not going to call again, anyway.

@Ron.Fehd.macro.maven: It will take me some time to work through that possibility (I find that macro and its description a bit non-intuitive and confusing). However, it seems to me from a naïve perspective that it will end up greatly increasing the complexity of my code, even if it gives me the correct solution.

Regular Contributor
Posts: 198

Re: Opening and closing datasets with %sysfunc in a macro

LOL

well, you asked how to reduce three lines of code

to one in the macro variable assignment statement.

save the macro in a file named value_of.sas

place it in either your project folder

or site folder

and add the following options statement to your autoexec.sas

*saved in project folder;

options sasautos = ('.' sasautos);

*saved in site folder;

options sasautos = ('<...>\macros' sasautos);

see also:

Batch processing under Windows - sasCommunity

Frequent Contributor
Posts: 98

Re: Opening and closing datasets with %sysfunc in a macro

:

Not sure what about this is making you "LOL". What you are proposing is less efficient for the context I am operating in. This macro is intended to be used by a variety of users operating on different computers, in which case it becomes ungainly to include autocall macros. Further, though I may be wrong on this, it is my understanding that autocalling a macro like that multiple times results in it compiling multiple times, whereas a macro in an open program file only compile once during the SAS session, regardless of how many times the program file is run.

Super User
Super User
Posts: 6,495

Re: Opening and closing datasets with %sysfunc in a macro

You seem to have the logic of when macro definitions will get compiled multiple times backwards.

If you use an autocall library of source code then SAS will search for and compile the macro only when it is referenced but it has not yet been defined.  If you never call it then it will never compile. If you call it 100 times it will be compiled only the first time.

If you include the macro definition inside each individual program module then it will get re-compiled every time that module runs, whether it has been compiled already or not.

You can also point to stored compiled macros in an SAS catalog, but I would rather have the source code available.

Regular Contributor
Posts: 198

Re: Opening and closing datasets with %sysfunc in a macro

simple hide the complexity in a macro which returns the value you want.

review the code in the macro value-of

and note that the parameter ''attribute''

can contain whichever attribute you want

http://www.sascommunity.org/wiki/Macro_nobs

so it can be used, per the test data

<source lang="sas">

%let nobs = %value_of(data=sashelp.class);

%let nvars = %value_of(data=sashelp.class

                      ,attribute=nvars);

%put &=nobs &=nvars;

</source>

Super User
Super User
Posts: 6,495

Re: Opening and closing datasets with %sysfunc in a macro

You cannot do it one statement because you need the file handle that the OPEN() function generates to use with both the ATTRN() and CLOSE() functions.

To me a long nested series of calls looks much more like a clunky workaround that nice clean series of simple steps like your %LET statements.

     %let did=%sysfunc(open(modout,i));

     %let varcount=%sysfunc(attrn(&did,nvars));

     %let did=%sysfunc(close(&did));

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 2207 views
  • 9 likes
  • 6 in conversation