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

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 

1 ACCEPTED SOLUTION

Accepted Solutions
LeonidBatkhan
Lapis Lazuli | Level 10

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.

View solution in original post

1 REPLY 1
LeonidBatkhan
Lapis Lazuli | Level 10

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 9866 views
  • 2 likes
  • 2 in conversation