topic Re: Need help with dropping a variable in SAS Programming

Need help with dropping a variable

Jmitchell — Mon, 06 Mar 2023 22:14:25 GMT

Hi, I am working on a project and I have to drop any variable where the variable name begins with ‘Street’ in this merge data set.

This is my syntax:

libname bhadlibs 'C:\Users\PhotonUser\My Files\Temporary Files';
data bhadlibs.employee_payroll;
set bhadlibs.employeepayroll;
proc sort;by employee_id;

/* Experienced difficulty on renaming multiple naming conventions and dropping "street. We should review this in class.
data bhadlibs.employee_donations;
set bhadlibs.employee_donations;
proc sort;by employee_id;

data bhadlibs.employee_addresses (rename=(street_id=street));
set bhadlibs.employee_addresses;
proc sort;by employee_id;

data bhadlibs.employee_addresses (rename=(street_number=street));
set bhadlibs.employee_addresses;
proc sort;by employee_id;
run;

data bhadlibs.employee_addresses (rename=(street_name=street2));
set bhadlibs.employee_addresses;
proc sort;by employee_id;
run;*/

data bhadlibs.employee_addresses;
set bhadlibs.employee_addresses2 (drop=street);
footnote1 'confidential';
run;

data mergepart;
merge bhadlibs.employee_payroll bhadlibs.employee_donations bhadlibs.employee_addresses;
by employee_id;
proc print;
run;

Re: Need help with dropping a variable

Quentin — Mon, 06 Mar 2023 22:25:18 GMT

You can use a variable name prefix to drop variables, e.g.:

data mergepart (drop=Street: ) ;

That will drop any variables that start with Street (case insensitive).

Re: Need help with dropping a variable

PaigeMiller — Mon, 06 Mar 2023 22:33:26 GMT

Interesting, you say you want to drop any variable whose name begins with 'street', but then you don't do anything like that, there are no DROP statements or options anywhere in your code until the very end.

Furthermore, this is highly redundant and wastes your time and computer time.

data bhadlibs.employee_payroll;
set bhadlibs.employeepayroll;

First, good practice in writing code means you should end each step with a RUN command.

data bhadlibs.employee_payroll;
set bhadlibs.employeepayroll;
run;
proc sort;by employee_id;
run;

Now also notice that your DATA step does not do anything. You had a dataset named BHADLIBS.EMPLOYEEPAYROLL and you create a brand new dataset where nothing changes except the name, which is now BHADLIBS.EMPLOYEE_PAYROLL. Data steps with nothing but a SET statement are generally not worth doing.

How about this to drop variables?

data bhadlibs.employee_payroll;
    set bhadlibs.employeepayroll(drop=street:);
run;

Notice variables are dropped in the SET statement (so its not just a SET statement now, it is a SET statement with a drop). The drop=street: (note the colon after street) indicates that all variables whose name begins with street are now dropped.

Or to simplify the entire thing into one data step, try this (which will require PROC SORTs before it can work)

data mergepart;
    merge bhadlibs.employee_payroll bhadlibs.employee_donations bhadlibs.employee_addresses;
    by id;
    drop street:;
run;

Re: Need help with dropping a variable

Jmitchell — Mon, 06 Mar 2023 23:00:56 GMT

Hi,

I'm still getting an error message. But I think there are a couple of other
issues. I keep getting an error saying my file doesn't exist.

How do I correct this?

Re: Need help with dropping a variable

Kurt_Bremser — Tue, 07 Mar 2023 06:10:52 GMT

Please post the complete log (all code and messages) of the failing step by copy/pasting it into a window opened with this button: