How do I Rename variables in a merged dataset that don't appear in First

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

How do I Rename variables in a merged dataset that don't appear in First

Hi,

I’m perplexed on how to use macro programming language to do the following:

 

I have dataset ONE which has 20 variables. This dataset is joined with another dataset (TWO) containing 800 variables, of which 15 are "grouped by" for the merge.  The result is dataset THREE, with 805 variables.

 

I want to RENAME the variables in THREE only if they aren’t the original variable names in dataset ONE. I’m adding a prefix, for example “abc_” to each variable name in THREE that is not a variable in ONE.  This needs to be generic since I don't want to hardcode the variables, in case we add any future variables to dataset ONE, or TWO.

 

Would anyone have a solution for me? I have searched online but can’t seem to find detailed info to help me with this issue.  Seems I need to create a varlist for both, and compare to see for those vars that aren’t in ONE, it’s ok to add the prefix to the variable name.

 

I'm using SAS 9.4.

 

Thanks in advance!! Smiley Happy

Monica


Accepted Solutions
Solution
‎08-02-2016 10:30 AM
New Contributor
Posts: 3

Re: How do I Rename variables in a merged dataset that don't appear in First

[ Edited ]

Thank you for this solution!  It works the way I need by changing the Having count (name) = 1.  Smiley Happy

View solution in original post


All Replies
Super User
Posts: 19,034

Re: How do I Rename variables in a merged dataset that don't appear in First

That's a hell of a lot of variables. 

 

Look at SASHELP.VCOLUMN to get the variables from each dataset that are dups. 

Untested below. If you search on here you should find some other examples, tested Smiley Happy

 

Data vars;
Set sashelp.vcolumn;

Where libname='WORK' and upper(memname) in ('DATA1' 'DATA2');

Keep libname name memname;
Run;

Proc SQL noprint;
Select distinct catx('=', name, catt('abc_', name)) into :rename_list separated by ' ' from vars
Group by name
Having count(name) >1;
Quit;

Tested sample here:

https://gist.github.com/statgeek/82d9f2854edc01560e0f

 

Solution
‎08-02-2016 10:30 AM
New Contributor
Posts: 3

Re: How do I Rename variables in a merged dataset that don't appear in First

[ Edited ]

Thank you for this solution!  It works the way I need by changing the Having count (name) = 1.  Smiley Happy

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 288 views
  • 0 likes
  • 2 in conversation