DATA Step, Macro, Functions and more

%SYSFUNC(OPEN) not finding data set when used in RSUBMIT within a macro

Reply
Occasional Contributor
Posts: 11

%SYSFUNC(OPEN) not finding data set when used in RSUBMIT within a macro

Hi,

 

I'm using PC SAS 9.4 and I'm attempting to rename a variable in a dataset using code similar to that found in the accepted solution HERE.

 

The problem that I'm facing is that I'm using RSUBMIT within the macro and SYSFUNC(OPEN) to open the dataset, but it's returning a value of zero for the dataset (and a value of 70021 when I attempt to get the variable number). Here's my attempted code so far:

 

 

OPTIONS MLOGIC MPRINT;

RSUBMIT;
     LIBNAME TEMP "/My_Data_Sets";
ENDRSUBMIT;

%MACRO Check_Ds(ds=, col=);
	
	%SYSLPUT ds = &ds;
	%SYSLPUT col = &col;
	
	RSUBMIT;
	
		%LET dsid = %SYSFUNC(OPEN(&ds));
		%LET column = %SYSFUNC(VARNUM(&dsid, &col));
		%LET rc = %SYSFUNC(CLOSE(&ds));
	
		%PUT ds = &ds;
		%PUT column = &column;
		%PUT rc = &rc;

	ENDRSUBMIT;

%MEND Check_Ds;

%Check_Ds(ds=TEMP.my_data_2016, col=SAL_PER_EMP);

 

The output for the above is:

  • ds = 0
  • column =  .
  • rc = 70021

The interesting thing is that if I use a PROC PRINT or PROC CONTENTS in the RSUBMIT with the &ds macro variable, it works fine and displays the records/content. I tried the following and the second PROC CONTENTS displayed the content, but the %ELSE condition was returned before that:

 

 

OPTIONS MLOGIC MPRINT;

RSUBMIT;
     LIBNAME TEMP "/My_Data_Sets";
ENDRSUBMIT;

%MACRO Check_Ds(ds=, col=);
	
	%SYSLPUT ds = &ds;
	%SYSLPUT col = &col;
	
	RSUBMIT;
	
		/*Note: 1st attempt to PROC CONTENTS */
		%IF %SYSFUNC(EXIST(&ds)) %THEN
			%DO;
				PROC CONTENTS DATA=&ds VARNUM;
				RUN;
			%END;
		%ELSE
			%PUT The data set &ds does not exist.;               

		/*Note: 2nd attempt to PROC CONTENTS */
		PROC CONTENTS DATA=&ds VARNUM;
		RUN;

		%LET dsid = %SYSFUNC(OPEN(&ds));
		%LET column = %SYSFUNC(VARNUM(&dsid, &col));
		%LET rc = %SYSFUNC(CLOSE(&ds));
	
		%PUT ds = &ds;
		%PUT column = &column;
		%PUT rc = &rc;

	ENDRSUBMIT;

%MEND Check_Ds;

%Check_Ds(ds=TEMP.my_data_2016, col=SAL_PER_EMP);

 

 

The output of the above is:

  • %IF condition %sysfunc(exist(TEMP.my_data_2016)) is FALSE
  • The data set TEMP.my_data_2016 does not exist.
  • ds = 0
  • column =  .
  • rc = 70021

Then, the second PROC CONTENTS runs successfully.

 

I know that I can wrap the entire macro in an RSUBMIT to get it to work (with some slight code clean up), but I'd like to find out why SYSFUNC appears to not be working when used in this fashion and whether or not there's a way to get it to work.

 

Thanks.

Super User
Super User
Posts: 7,970

Re: %SYSFUNC(OPEN) not finding data set when used in RSUBMIT within a macro

I will repeat what I said in that post, the code below is far simpler:

data _null_;
     set sashelp.vcolumn (where=(libname="WORK" and memname="TEST" and name="COUNT2"));
     call execute('data test 2; (rename=(count2 = amount2)); run;');
run;

Is there a reason you cannot use Base SAS?  If so provide specific examples.

Occasional Contributor
Posts: 11

Re: %SYSFUNC(OPEN) not finding data set when used in RSUBMIT within a macro

[ Edited ]

I'm fairly new to SAS and the data step (I primarily use PROC SQL), so I'm not 100% sure I completely understand the code you provided.

 

In your code, you're setting the data set to the SASHELP table. If the variable exists in the SASHELP table, it appears as though you're renaming the variable in another data set ('data test2'). Does this assume that the 'data test2' data set already exists? Or were you indicating in your original post that the 'data test2' data set will be only created if the variable is found in the SASHELP table?

 

I didn't explicitly indicate it in my original post, but I would like to read the first data set to determine if the variable exists in the data set, then set that data set equal to a new/temp data set, and then rename the variable in the new/temp data set. Would I just need to modify your code to the following to make that work?

 

/* Original data set: TEMP.my_data_2016 */
/* Original variable name: SAL_PER_EMP */
/* New data set (after variable rename): WORK.revised_data_2016 */
/* New variable name: SAL_BY_YEAR */
data _null_;
     set sashelp.vcolumn (where=(libname="TEMP" and memname="my_data_2016" and name="SAL_PER_EMP"));
     call execute('data WORK.revised_data_2016; set TEMP.my_data_2016 (rename=(SAL_PER_EMP = SAL_BY_YEAR)); run;');
run;

Thanks.

Occasional Contributor
Posts: 11

Re: %SYSFUNC(OPEN) not finding data set when used in RSUBMIT within a macro

I think I answered my own questions. When I used the 'set' option to point to the original data set, it appears to have worked successfully.

Super User
Super User
Posts: 7,970

Re: %SYSFUNC(OPEN) not finding data set when used in RSUBMIT within a macro

Well, am glad you got it working.  Some points to consider however - Base SAS (or datastep) is the foundation to everything in SAS, you should consider a through knowledge of that as primary if you are going to use SAS.  Proc sql is and additional component - not needed in anyway to use SAS to its fullest, but can be a useful companion, much the the vastly over abused macro system whose only function is to generate Base SAS code.

Super User
Posts: 7,809

Re: %SYSFUNC(OPEN) not finding data set when used in RSUBMIT within a macro

Macro triggers are always dealt with first.

When SAS prepares the code for remote submit, the %let and the %sysfunc is resolved locally, as are the %put's.

If you just use &ds in base SAS code, the &ds is resolved locally and the resulting text submitted remotely.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 5 replies
  • 480 views
  • 0 likes
  • 3 in conversation