Copy values of one column to another column in same dataset

[DataYes]

Hello, i have a data set which i created in a work library. i want to drop one column and also copy the values of dropped column in another column which is blank. how do i do that?

here is an example.

Suppose i have a dataset which has 4 columns:

Name      ID           address      ID_2

Sam                        xyz             123

Bob                         abc             456

i want to be able to modify the dataset to drop the column "ID_2" and move the values from the column "123" and "456" to "ID"

how can i acheive it using proc sql?

here is what i have done so far

data want;

set have;

ID=ID_2;

run;

the issue with this stateent is that it only captures the first letter of the column "ID_2". so using the above example, when i run the code mentioned above, i get:

Name    ID   Address    ID_2

SAM      1     xyz           123

Boc        4     abc           456

please guide

thanks in advance

here is 

[LeonidBatkhan · Accepted solution]

Hi DataYes,

You are getting this because your ID variable on your data set HAVE has length of 1. If you assign long enough length it will work. For example, the following data step will work fine:

data want;
   length ID $3;
   set have;
   ID=ID_2;
   drop ID_2;
run;

Or you can do it this way:

data want;
   set have (drop=ID rename=(ID_2=ID));
run;

If you need a certain variable order on your data set you can achieve it with the retain statement placed before set statement:

data want;
   retain Name ID address;
   set have (drop=ID rename=(ID_2=ID));
run;

Hope this helps.