Hell to all,
In order to keep record of the source of the variables when merging multiple data sets together, I would like to add the data set name to the original variable labels. For example:
variable name : income, which is in data set "W2"
original label: total income
desired label: total income W2
Put it in another way, I would like to add the words of data set name to all the variables in a data set.
The following is the codes that I have come up. The codes do not change the labels. I wonder if anyone could give me some suggestion how to revise the codes or any better idea to do this task? Thank you!
*--------------------------------------------;
data _null_;
call execute("data &syslast.; set &syslast. end=eof;");
call execute('array abc{*} _character_;');
call execute('array def{*} _numeric_;');
call execute('do i=1 to dim(abc);');
if eof then call execute('label abc{i}='||'"'strip(vlabel(abc{i}))||'&syslast";');
call execute('end;');
call execute(`do j=1 to dim(def);');
if eof then call execute(`label def{j}='||'"'strip(vlabel(def{j}))||'&syslast";');
call execute('end:');
call execute('run;');
*--------------------------------------;
/*CREATE SOME SAMPLE DATA, NAMELY _H1, _H2*/
data _h1;
set sashelp.class;
run;
data _h2;
set sashelp.class;
run;
/*Put the names of the tables into macro variable DNAME, modify the query accordingly to reflect your library and tables of interest,
when tables locate in different libraries, the code needs to be tweaked*/
PROC SQL;
SELECT DISTINCT MEMNAME INTO :DNAME SEPARATED BY ' ' FROM DICTIONARY.TABLES WHERE LIBNAME='WORK' AND MEMNAME EQT '_H';
QUIT;
/*Relabel it. Rename is only done because the sample tables share the same variable names. This step should precede your merge or join.
Proc Dataset is more efficient than data step in this context, as it only modifies the header/metadata*/
%MACRO LB;
%DO I=1 %TO %SYSFUNC(COUNTW(&DNAME));
%LET DN=%SCAN(&DNAME,&I);
PROC SQL;
SELECT DISTINCT CATS(NAME, "=", NAME,"&DN.") INTO :VNAME_&DN SEPARATED BY ' ' FROM DICTIONARY.COLUMNS WHERE LIBNAME='WORK' AND MEMNAME="&DN"
;
SELECT DISTINCT CATS(NAME,"&DN.", "=", "'", NAME, "&DN.", "'") INTO :LNAME_&DN SEPARATED BY ' ' FROM DICTIONARY.COLUMNS WHERE LIBNAME='WORK' AND MEMNAME="&DN"
;
QUIT;
PROC DATASETS LIBRARY=WORK NOLIST;
MODIFY &DN.;
RENAME &&VNAME_&DN;
LABEL &&LNAME_&DN;
QUIT;
%END;
%MEND;
%LB;
/*CREATE SOME SAMPLE DATA, NAMELY _H1, _H2*/
data _h1;
set sashelp.class;
run;
data _h2;
set sashelp.class;
run;
/*Put the names of the tables into macro variable DNAME, modify the query accordingly to reflect your library and tables of interest,
when tables locate in different libraries, the code needs to be tweaked*/
PROC SQL;
SELECT DISTINCT MEMNAME INTO :DNAME SEPARATED BY ' ' FROM DICTIONARY.TABLES WHERE LIBNAME='WORK' AND MEMNAME EQT '_H';
QUIT;
/*Relabel it. Rename is only done because the sample tables share the same variable names. This step should precede your merge or join.
Proc Dataset is more efficient than data step in this context, as it only modifies the header/metadata*/
%MACRO LB;
%DO I=1 %TO %SYSFUNC(COUNTW(&DNAME));
%LET DN=%SCAN(&DNAME,&I);
PROC SQL;
SELECT DISTINCT CATS(NAME, "=", NAME,"&DN.") INTO :VNAME_&DN SEPARATED BY ' ' FROM DICTIONARY.COLUMNS WHERE LIBNAME='WORK' AND MEMNAME="&DN"
;
SELECT DISTINCT CATS(NAME,"&DN.", "=", "'", NAME, "&DN.", "'") INTO :LNAME_&DN SEPARATED BY ' ' FROM DICTIONARY.COLUMNS WHERE LIBNAME='WORK' AND MEMNAME="&DN"
;
QUIT;
PROC DATASETS LIBRARY=WORK NOLIST;
MODIFY &DN.;
RENAME &&VNAME_&DN;
LABEL &&LNAME_&DN;
QUIT;
%END;
%MEND;
%LB;
Hello Hai Kuo,
The codes you provided works very well. Thank you a lot for your help! I appreciate it.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.