topic Re: Is there one way to drop last variable always? in SAS Programming

Is there one way to drop last variable always?

dali74 — Fri, 17 Nov 2017 09:36:48 GMT

I want to drop last variable but i dont know how.

Is it possible to do in SAS?

Re: Is there one way to drop last variable always?

Shmuel — Fri, 17 Nov 2017 09:39:52 GMT

What do you mean by last variable?

Can you post a sample of data and point at what do you wand to drop ?!

Re: Is there one way to drop last variable always?

dali74 — Fri, 17 Nov 2017 09:43:44 GMT

data matriz;
INPUT x1-x3 var @@;
datalines;
20	6	14	1
21	6	18	2
20	6	14	3
24	6	45	5
;
run;

If we have this code i want to drop variable named "var". but i can have more variables in the future and i want drop last variable always, because his position is always the last one.

Re: Is there one way to drop last variable always?

PeterClemmensen — Fri, 17 Nov 2017 09:50:59 GMT

There is probably a smarter way, but you could do something like this

data have;
input var1-var3;
datalines;
1 2 3
4 5 6
7 8 9
;

ods select Variables;
proc contents data=have;
	ods output Variables=var;
run;

proc sort data=var;
	by Num;
run;

data _null_;
	set var;
	by num;
	if last.num then call symputx('lastvar', Variable);
run;

%put The last variable is &lastvar;

data want;
	set have;
	drop &lastvar;
run;

Re: Is there one way to drop last variable always?

dali74 — Fri, 17 Nov 2017 10:14:27 GMT

Yes it works fine but i´m using this code and it always put 0

proc sql;
    select  count (varnum) into :numvar
    from    dictionary.COLUMNS
	 where upcase(LIBNAME) =  "WORK"  and
     upcase(MEMNAME) = "matriz";
quit;

First i´m looking for number of vars and i will delete it after.

Re: Is there one way to drop last variable always?

PeterClemmensen — Fri, 17 Nov 2017 10:15:40 GMT

1. this code returns zero because you use the UPCASE Function and do not upcase the string "matriz". It should be

proc sql;
    select  count (varnum) into :numvar
    from    dictionary.COLUMNS
	 where upcase(LIBNAME) =  "WORK"  and
     upcase(MEMNAME) = "MATRIZ";
quit;

2. Why? This code will give you the number of colums in your data set. How do you go from there to actually dropping the numvar'th variable?

Re: Is there one way to drop last variable always?

Kurt_Bremser — Fri, 17 Nov 2017 10:16:34 GMT

Retrieve the information from dictionary.columns, and store into a macro variable used for dropping:

data matriz;
INPUT x1-x3 var;
datalines;
20 6 14 1
21 6 18 2
20 6 14 3
24 6 45 5
;
run;

proc sql noprint;
select name into:lastvar
from dictionary.columns
where libname='WORK' and memname='MATRIZ'
having varnum=max(varnum)
;
quit;

%put &lastvar; * for control purposes;

data matriz_new;
set matriz;
drop &lastvar;
run;

Re: Is there one way to drop last variable always?

RW9 — Fri, 17 Nov 2017 10:29:03 GMT

Out of interest, what is the logical reason for always removing the last variable? It sounds a bit like your doing some processing on lots of datasets and then trying to fix it later on. Its highly likely that a simple change to the process would remove the need for this at all.

Re: Is there one way to drop last variable always?

dali74 — Fri, 17 Nov 2017 10:59:35 GMT

Problaby you are right. I need to save last variable in other dataset.

Re: Is there one way to drop last variable always?

Ksharp — Fri, 17 Nov 2017 13:03:59 GMT

data class;
 set sashelp.class;
run;

proc transpose data=class(obs=0) out=temp;
var _all_;
run;
data _null_;
 set temp point=nobs nobs=nobs;
 call execute(cat('data want;set class;drop ',_name_,';run;'));
 stop;
run;

Re: Is there one way to drop last variable always?

rajeshalwayswel — Sun, 19 Nov 2017 10:17:47 GMT

How weight variable deleted? As per my understanding

The NOBS = option creates a variable which contains the total number of observations in the input data set(s). If multiple data sets are listed in the SET statement, the value in the NOBS = variable are the total number of observations in all the listed data sets.

This is read in online. Can some one explain it...

Re: Is there one way to drop last variable always?

Kurt_Bremser — Sun, 19 Nov 2017 10:28:30 GMT

Proc transpose (when used in this way) creates a dataset with observations (rows) for columns, so the last observation will contain the name of the last variable in _name_.

The point=nobs nobs=nobs option combination will read exactly this last observation from the transposed dataset.

Note that @Ksharp's solution reads the dataset twice (and additionally writes the transposed dataset), while mine (using the table metadata) only needs one read of the complete dataset. I guess it's just to show how many ways one can find in SAS to solve a specific issue.

Re: Is there one way to drop last variable always?

rajeshalwayswel — Sun, 19 Nov 2017 10:45:45 GMT

Thank you... and one more thing in your code SQl "select" statement I seen name variable but it not found in source data set How it going to work...(I'm seen X1-X3 and var variable's)

Re: Is there one way to drop last variable always?

PeterClemmensen — Sun, 19 Nov 2017 10:52:25 GMT

Check this out 🙂

How to View Dictionary Tables

Re: Is there one way to drop last variable always?

Kurt_Bremser — Sun, 19 Nov 2017 10:56:25 GMT

@rajeshalwayswel wrote:

Thank you... and one more thing in your code SQl "select" statement I seen name variable but it not found in source data set How it going to work...(I'm seen X1-X3 and var variable's)

Look closely. The SQL reads from dictionary.columns, which is a dynamically populated meta-dataset. Google for "sas dictionary tables" to see what you can get from them.

Although syntactically not reading from the source dataset, technically it reads the file header and retrieves the dataset's structural information.