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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

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

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


Accepted Solutions
Solution
‎07-18-2014 04:34 PM
Respected Advisor
Posts: 3,156

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

/*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 Smiley Very HappyNAME 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;

View solution in original post


All Replies
Solution
‎07-18-2014 04:34 PM
Respected Advisor
Posts: 3,156

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

/*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 Smiley Very HappyNAME 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;

New Contributor
Posts: 2

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

Hello Hai Kuo,

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


🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 244 views
  • 1 like
  • 2 in conversation