SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Retain original format and label after renaming variables

Accepted Solution Solved
Reply
Regular Contributor
Posts: 215
Accepted Solution

Retain original format and label after renaming variables

Hi there.

 

I used the macro %macro replaceprefix(lib,dsn,start,end,oldprefix,newprefix); to perform the prefix addition on selected variables and it is accesible from http://support.sas.com/resources/papers/proceedings09/075-2009.pdf. However, it doesn't retain the format and label of the variables. 

 

Can I please ask your help to shed some lights on this? Thank you very much.

 


Accepted Solutions
Solution
‎01-30-2017 09:58 AM
Regular Contributor
Posts: 215

Re: Retain original format and label after renaming variables

Hi @ankit___gupta,

Please find modified SAS code below (last 2 %let statements within the first do loop, label and format statement in the second datastep).

HTH. Thank you.

 

/*
Paper 075-2009
Renaming in Batches
Vincent Weng, Educational Testing Service, Princeton, NJ
Ying Feng, CTB/McGraw-Hill, Monterey, CA 

accessible via http://support.sas.com/resources/papers/proceedings09/075-2009.pdf
*/

/* Replacing Prefix on Selected Variables */
%macro replaceprefix(lib,dsn,start,end,oldprefix,newprefix);
proc contents data=&lib..&dsn.; title 'before renaming'; run;
data temp; set &lib..&dsn.; run;
%LET ds=%SYSFUNC(OPEN(temp,i));
%LET ol=%LENGTH(&oldprefix.);
%DO i=&start %TO &end;
	%LET dsvn&i=%SYSFUNC(VARNAME(&ds,&i));
	%LET l=%LENGTH(&&dsvn&i);
	%LET vn&i=&newprefix.%SUBSTR(&&dsvn&i,&ol+1,%EVAL(&l-&ol));

	%LET dsvnlab&i=%BQUOTE(%SYSFUNC(VARLABEL(&ds,%SYSFUNC(varnum(&ds,&&dsvn&i)))));
	%LET dsvnfmt&i=%SYSFUNC(VARFMT(&ds,%SYSFUNC(varnum(&ds,&&dsvn&i))));

%end;
data &lib..&dsn.;
	set temp;

	%DO i=&start %TO &end; 
		&&vn&i=&&dsvn&i; 
		label &&vn&i=%STR(&&dsvnlab&i);
		format &&vn&i &&dsvnfmt&i.;
		drop &&dsvn&i; 
	%end;
	%let rc=%SYSFUNC(CLOSE(&ds));
	proc contents data=&lib..&dsn.;
	title 'Replacing Prefix on Selected variables ';
run;
%mend replaceprefix;

View solution in original post


All Replies
Contributor
Posts: 46

Re: Retain original format and label after renaming variables

Hi Wong,

 

Why don't you set the format and label once more after the renaming.

 

In the  %macro replaceprefix(lib,dsn,start,end,oldprefix,newprefix); try adding the following.

                          

        

DATA    <lib>.<datasetname>;
    SET <lib>.<datasetname>;
        FORMAT <variable name> <format>;
	INFORMAT  <variable name> <format>;
        LABEL <label u want to give>;
RUN;

 

 

Hope it helps.

Super User
Posts: 5,071

Re: Retain original format and label after renaming variables

Shed some light?  Yes.  Fix?  No.

 

You'll have to inspect the details within the macro you referenced.  It uses a DATA step to copy the original variables to new variables (then subsequently drops the original variables).  When you do that, formats and labels do not get copied, just the data values.

 

There are other macros within the paper that don't do that, using PROC DATASETS instead of a DATA step.  But those macros don't perform the exact same function as the one you are using.

Regular Contributor
Posts: 215

Re: Retain original format and label after renaming variables

Problem's now solved after adding a few lines of SAS statements to the macro.

Thank you Smiley Happy

Contributor
Posts: 46

Re: Retain original format and label after renaming variables

Hi wong ,

 

So what was the solution ? Can u share ?

 

 

Solution
‎01-30-2017 09:58 AM
Regular Contributor
Posts: 215

Re: Retain original format and label after renaming variables

Hi @ankit___gupta,

Please find modified SAS code below (last 2 %let statements within the first do loop, label and format statement in the second datastep).

HTH. Thank you.

 

/*
Paper 075-2009
Renaming in Batches
Vincent Weng, Educational Testing Service, Princeton, NJ
Ying Feng, CTB/McGraw-Hill, Monterey, CA 

accessible via http://support.sas.com/resources/papers/proceedings09/075-2009.pdf
*/

/* Replacing Prefix on Selected Variables */
%macro replaceprefix(lib,dsn,start,end,oldprefix,newprefix);
proc contents data=&lib..&dsn.; title 'before renaming'; run;
data temp; set &lib..&dsn.; run;
%LET ds=%SYSFUNC(OPEN(temp,i));
%LET ol=%LENGTH(&oldprefix.);
%DO i=&start %TO &end;
	%LET dsvn&i=%SYSFUNC(VARNAME(&ds,&i));
	%LET l=%LENGTH(&&dsvn&i);
	%LET vn&i=&newprefix.%SUBSTR(&&dsvn&i,&ol+1,%EVAL(&l-&ol));

	%LET dsvnlab&i=%BQUOTE(%SYSFUNC(VARLABEL(&ds,%SYSFUNC(varnum(&ds,&&dsvn&i)))));
	%LET dsvnfmt&i=%SYSFUNC(VARFMT(&ds,%SYSFUNC(varnum(&ds,&&dsvn&i))));

%end;
data &lib..&dsn.;
	set temp;

	%DO i=&start %TO &end; 
		&&vn&i=&&dsvn&i; 
		label &&vn&i=%STR(&&dsvnlab&i);
		format &&vn&i &&dsvnfmt&i.;
		drop &&dsvn&i; 
	%end;
	%let rc=%SYSFUNC(CLOSE(&ds));
	proc contents data=&lib..&dsn.;
	title 'Replacing Prefix on Selected variables ';
run;
%mend replaceprefix;
☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 348 views
  • 0 likes
  • 3 in conversation