How to do variable modifications in a SAS dataset?

Hello, I am trying to perform variable modifications.

I am trying to recreate SAS work dataset Data1 by performing the following:
1. Use SAS work dataset Data1 as input.
2. Drop the following variables, if they exist: Var1 Var2 Var3
3. Rename Var5 to Var15.
4. Create the Var5 variable as NUM (type) and 8 (length)
5. Convert Var15 to its numeric equivalent and store in Var5.
6. Maintain SAS binary compression.

So, I am not sure on step #4 & #5. Please see my code in progress:

data Data1 (compress=binary);
  drop Var1 Var2 Var3;
 set dir.Data1;

Var5=Var15;
.....

run;

Open in new window


Any help will be greatly appreciated!
labradorchikAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aloysius LowCommented:
data Data1 (
    drop = Var1 Var2 Var3 /*#2*/
    rename = (Var5 = Var15) /*#3*/
    compress=binary /*#6*/);
  set Data1; /*#1*/
  length Var5 8.; /*#4*/

  Var5=input(Var15, best.); /*#5*/
run;
labradorchikAuthor Commented:
Thank you very much, lowaloysius!
Quick question: do I need to check in the code if Var1, Var2, and Var3 are actually there in the dataset or I will not get any error messages if I just have those variables dropped as you written above?
HuaMin ChenProblem resolverCommented:
Here are some example
data gems;
   input Name $ Color $ Carats Owner $;
   datalines;
emerald green 1 smith
sapphire blue 2 johnson
ruby red 1 clark
;

data lollipops;
   Flavor="Cherry";
   attrib Flavor format=$10.;
run;

data lollipops;
   Flavor="Cherry";
   attrib Flavor format=$10.;
run;

Open in new window

Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Aloysius LowCommented:
if the variable *may* not exist and you want to avoid an error message then use the drop statement instead in the code:

data Data1 (
    rename = (Var5 = Var15) /*#3*/
    compress=binary /*#6*/);
  set Data1; /*#1*/
  length Var5 8.; /*#4*/

  Var5=input(Var15, best.); /*#5*/
  drop Var1 Var2 Var3; /*#2*/ 
run;

Open in new window

IanStatisticianCommented:
Slight error with the renames.  Done at wrong time.  That code will not work.

Also the drop of variables that dont exist is not keeping to requirement 2.
Try the following, and try using var1 var2 instead of var10 and var20 in the input line.


data Data1;
	length var5 $ 6;
	input var10 var20 var5 var19;

cards;
1 2 5 10
10 20 50 100
11 21 51 101
0 . . .
;;;;
run;

proc sql noprint;
** Ensure macro variable is empty **;
%let	names=;

	** Find out all the variables that are in the data set with names var1, var2 and var3 **;
	** Could be 0, 1, 2 or 3.  Put the list of names into macro variable "names" **;
	select	name
	into	:Names separated by ' ' 
	from	sashelp.VCOLUMN
	where	(memName="DATA1") and
			(libName = "WORK") and
			(upcase(name) in ("VAR1", "VAR2", "VAR3"));
	** Need to use uppercase for variable name as sas preserves case (but is insensitive in usage) **;

quit;


data Data1(compress=binary);	** Compress on writing new data set requirement 6 **;

	ZZZZZZ = 0;					** Dummy statement to make drop work in all cases **;
	set		Data1(rename = (Var5 = Var15));
								** Use Data1, Requirement 1 **;
								** Rename on input Requirement 3 **;

	length	Var5 8;				** Requirement 4 - This is not necessary as it is current default **;

	Var5=input(Var15, best.);	** Requirement 5 **;

	drop &names ZZZZZZ;			** Requirement 2 **;
	drop var15;					** implied by requirement 5 **;

run;

***
* Dropping dummy variable ZZZZZ means that even if &names
* contains nothing the drop is still syntacially correct
***:

***
* The 2 drop statements could be combined (and use of variable ZZZZZZ eliminated
* if the second is really required 
***;

Open in new window

Aloysius LowCommented:
yes - as correctly pointed out by ShannonEE, the rename should be at the SET statement instead of DATA statement.. confused over the same name..

data Data1 (
    compress=binary /*#6*/);
  set Data1 (rename = (Var5 = Var15) /*#3*/); /*#1*/
  length Var5 8.; /*#4*/

  Var5=input(Var15, best.); /*#5*/
  drop Var1 Var2 Var3; /*#2*/
run;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
labradorchikAuthor Commented:
Lowaloysius and ShannonEE,
Thank you very much for your suggestions and explanations!

I was totally confused where to place that drop statement.
Thank you again!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.