BookmarkSubscribeRSS Feed
beacon
Obsidian | Level 7

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.

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

beacon
Obsidian | Level 7

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.

beacon
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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.

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
  • 1877 views
  • 0 likes
  • 3 in conversation