DATA Step, Macro, Functions and more

Remove columns with characters

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 78
Accepted Solution

Remove columns with characters

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.

Accepted Solutions
Solution
‎03-02-2017 01:09 AM
Valued Guide
Posts: 505

Re: Remove columns with characters

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,018

Re: Remove columns with characters

Please show data before and after.

Frequent Contributor
Posts: 78

Re: Remove columns with characters

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


Frequent Contributor
Posts: 78

Re: Remove columns with characters

Fyi the file is a csv
PROC Star
Posts: 7,467

Re: Remove columns with characters

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

 

Solution
‎03-02-2017 01:09 AM
Valued Guide
Posts: 505

Re: Remove columns with characters

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 154 views
  • 0 likes
  • 4 in conversation