CODEWITHSUNDEEP
sqlsasrelational-database
Mark Romano
Mark Romano

Reputation: 711

Dropping columns based on the column number - SAS

I have a dataset containing the following columns:

ID, value, value1, value2

I want to keep columns ID and value. I want to drop the rest based on numerical position. So value1 and value2 are equivalent to 3 and 4. I want to drop anything greater than or equal to 3.

Is there any syntax in the SAS/SQL repertoire that will allow me to reference and drop the columns based on their position.

DATA TEST;
   INPUT id $ value value1 value2;
   DATALINES;
a 10 1 8
a 13 2 11
a 14 3 23
b 15 4 44
b 44 5 45
c 64 6 67
c 32 6 47
d 12 7 895
;
RUN;

Thanks.

Upvotes: 0

Views: 3423

Answers (2)

Stu Sztukowski
Stu Sztukowski

Reputation: 12934

One way would be to read the columns from sashelp.vcolumn, then save the first 4 columns into a macro variable.

proc sql inobs=4;
    select name
    into :keepcols separated by ','
    from sashelp.vcolumn
    where libname = 'WORK'
          AND memname = 'TEST';

proc sql;
    select &keepcols
    from TEST;
quit;

If you cannot use the dictionary table for some reason, you can use proc transpose to get the columns:

proc transpose data=TEST(obs=0)
               out=cols;
run;

proc sql noprint inobs=4;
    select _NAME_
    into :keepcols separated by ','
    from cols;
quit;

proc sql;
    select &keepcols
    from TEST;
quit;

Upvotes: 3

Ludwig61
Ludwig61

Reputation: 141

Proc Contents is often quicker to execute than the dictionary tables.

PROC CONTENTS DATA=sourcetable OUT=temptable NOPRINT ;
RUN ;

PROC SQL ;

  SELECT name
    INTO :selectclause SEPARATED BY ', '
    FROM temptable
    WHERE varnum LE 3
    ORDER BY npos
  ;

  CREATE TABLE shorttable AS
    SELECT &selectclause
      FROM sourcetable
  ;

QUIT ;

Upvotes: 2

Related Questions