Replace a variable name conditionally

[mmhxc5]

Hi all,

I have  a SAS dataset of over 600,000 data points. A variable called Structure_Number_008 is the structure identifier. Some strucures has got Old_name and New_name both due to some rules and both names are available for some structures in the SAS data set.

I have another excel file which has the Old_name and New_name correlation for all structures in two columns.

In the SAS data set, I want to replace all Old_name with its New_name while keeping the structures which has only New_name.

I would be grateful if anyone could help me to do the job.

Thanks,

[Amir]

Are you wanting to change data or the name of the variable that holds the data?

Please supply some example data before and after the change, using datalines in SAS so that others can use it.

Amir.

[andreas_lds]

Fully agree to @Amir: title and description don't match, so please clarify what you have and what you need, preferable with example dataset (NOT excel-files).

[mmhxc5]

@Amirand @andreas_lds, thank you for asking more clarification. I want to replace only the Old_name and keep all the data relating to the name while changing it to the New_name.

The SAS data set has Structure_Number_008 which is a mixture of Old_name and New_name such as the

The Old_name is available in the excel file which correlates it to the New_name as below.

I want to replace all Old_names in the SAS file if  the Old_name and its New_name is available in the excel file. I just want to replace the name and keep all the data related to the Old_name with the New_name.

[jffeudo86]

Please be careful with your example. You may be posting information that you should not post. Just a thought. If you need to delete it please do so.

[Tom · Accepted solution]

It sounds like you want to change the value of a variable based on some lookup table?

So if Structure_Number_008 is equal to 'XXXX' and there is record in the lookup table that says XXXX is really YYYY want to change from XXXX to YYYY?

If so then a format is an easy way to do that.

proc format ;
value $rename
 'XXXX' = 'YYYY'
;
quit;

data want;
  set have ;
  Structure_Number_008 = put(Stucture_Number_008,$rename.);
run;

There are ways to convert a lookup table into the dataset structure that you can use to create a format.

So if you had a dataset that looked like:

data renames;
  infile cards dsd dlm='|' truncover ;
  input oldname :$200. newname :$200.;
cards;
XXXX|YYYY
A b C|ABC
;

You could make a format from that by first creating a dataset in the right form and then passing the dataset to PROC FORMAT.

data format;
  retain fmtname '$rename' ;
  set renames;
  start=oldname;
  label=newname;
  keep fmtname start label;
run;
proc format cntlin=format;
run;

[mmhxc5]

@Tom, Thank you for your help. You have catched my point perfectly. Here is what I want.

data NBI_data;
input Structure_Number_08 Substrucuture_condition  Year_built
datalines;
11700121    021 8 1910
11700121    021 8 1910 
11700121    021 7 1910
11700121    021 7 1910
11700121    021 6 1910
2630 8 2012
2630 8 2012
2630 8 2012
2630 7 2012
2630 7 2012
.
.
.
;
run;

data_Nbi_Correlation;
input Old_structure_Number_008 Strucutre_number_008;
datalines
11700121    021 16349
.
.
.
;
run;

*/The data I want shall have all values renamed with new values and also keep those already with new values.*/;
Data want;
input Structure_Number_08 Substrucuture_condition  Year_built
datalines;
16349 8 1910
16349 8 1910 
16349 7 1910
16349 7 1910
16349 6 1910
2630 8 2012
2630 8 2012
2630 8 2012
2630 7 2012
2630 7 2012
   

I would be happy if you demonstrate the code based on my example. The value (11700121 021) is one value.

[Tom]

You can work it out yourself.  You table NBI_CORRELATION is like the RENAMES table in my example. 

You could use it to make a format like I did.

Or just use it in some other way.

[andreas_lds]

To rename a variable use either the rename statement in a data-step or proc datasets.