BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AmandaA
Calcite | Level 5

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

View solution in original post

2 REPLIES 2
Haikuo
Onyx | Level 15

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

AmandaA
Calcite | Level 5

Hello Hai Kuo,

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


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
  • 2 replies
  • 877 views
  • 1 like
  • 2 in conversation