topic Re: How to add data set name to all the variable labels? in SAS Programming

How to add data set name to all the variable labels?

AmandaA — Fri, 18 Jul 2014 15:32:02 GMT

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;');

*--------------------------------------;

Re: How to add data set name to all the variable labels?

Haikuo — Fri, 18 Jul 2014 20:34:25 GMT

/*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;

Re: How to add data set name to all the variable labels?

AmandaA — Fri, 18 Jul 2014 20:57:49 GMT

Hello Hai Kuo,

The codes you provided works very well. Thank you a lot for your help! I appreciate it.