topic Re: Remove columns with characters in SAS Programming

Remove columns with characters

Gil_ — Thu, 23 Feb 2017 17:28:59 GMT

Hi I have a data set looks like this
Date Tracking. Col1 col2 col3 col4 col5 col6.col7 col8
01/01/16 2z. $$ bag. Atm. { BAG. STM $$ {
I need to remove the cold where the symbols $$ or { exist and just keep where bag and atm exist.

Re: Remove columns with characters

mkeintz — Thu, 23 Feb 2017 17:45:44 GMT

Please show data before and after.

Re: Remove columns with characters

Gil_ — Thu, 23 Feb 2017 18:39:19 GMT

Ok
date. Tracking col1. Col2. Col3 col4. Col5 col6 col7
01/01/16. 1z. $$ bag. Atm $$ { bag atm
Output

Date. Tracking col2 col3. Col6. col7
01/01/26. 1 z Bag. Atm. Bag. Atm
The bag has 7 characters atm has 7 character after the word bag atm ..thanks for quick response

Re: Remove columns with characters

Gil_ — Thu, 23 Feb 2017 18:41:51 GMT

Fyi the file is a csv

Re: Remove columns with characters

art297 — Thu, 23 Feb 2017 20:19:00 GMT

What you have and want isn't clear from what you've shown. Provide the have and want example in the form of two data steps.

Art, CEO, AnalystFinder.com

Re: Remove columns with characters

rogerjdeangelis — Thu, 23 Feb 2017 20:55:06 GMT

Drop columns that contain '$$' or '('

inspired by
https://goo.gl/2GydUH
https://communities.sas.com/t5/Base-SAS-Programming/Remove-columns-with-characters/m-p/335357

HAVE
====

Up to 40 obs WORK.HAVE total obs=1

Obs      DATE      TRACKING    COL1    COL2    COL3    COL4    COL5    COL6    COL7    COL8

 1     01/01/16      2z.        $$     bag.    Atm.     {      BAG.    STM      $$      {

WANT
====

Up to 40 obs WORK.WANT total obs=1

bs      DATE      TRACKING    COL2    COL3    COL5    COL6

1     01/01/16      2z.       bag.    Atm.    BAG.    STM

DETAILS
=======

  COL1='$$' so drop
  COL7='$$' so drop

WORKING CODE
============

      rc=dosubl('
       if chrs[i]="$$" then rol=catx(" ",rol,vname(chrs[i]));
       call symputx("drp",rol);

      set have(drop=&drp);


FULL SOLUTION
=============

Create some data;
data have;
input (Date  Tracking Col1 col2 col3 col4 col5 col6 col7 col8) ($);
cards4;
01/01/16 2z. $$ bag. Atm. { BAG. STM $$ {
;;;;
run;quit;

%symdel drp;
data want;
 if _n_=0 then do;
   rc=%sysfunc(dosubl('
    data out;
       length rol $200;
       do until (dne);
          set have end=dne;
          array chrs[*]  col:;
          do i=1 to dim(chrs);
            put chrs[i] ;
            if chrs[i]="$$" or chrs[i]="{" then rol=catx(" ",rol,vname(chrs[i]));
          end;
       end;
       call symputx("drp",rol);
       stop;
    run;quit;
 '));
 end;

 set have(drop=&drp);
 drop rc;

run;quit;