CODEWITHSUNDEEP
sasproc-sql
Code Shode
Code Shode

Reputation: 1

How to remove first 4 and last 3 letters, digits or punctuation using PROC SQL in SAS Enterprise Guide?

I am trying to remove 009, and ,N from 009,A,N just to obtain the letter A in my dataset. Please guide how do I obtain such using PROC SQL in SAS or even in the data step. I want to keep the variable name the same and just remove the above-mentioned digits, letters and punctuation from the data.

Upvotes: 0

Views: 696

Answers (2)

Richard
Richard

Reputation: 27498

What you code really depends on what other values there are in the other rows of the data set.

From the one sample value you provide the following code could be what you want.

data have;
  input myvar $char80.;
  datalines;
009,A,N
009-A-N
Okay
run;

proc sql;
  update work.have
  set myvar = scan(myvar,2,',')
  where count(myvar,',') > 1
;

enter image description here

Upvotes: 1

AlexK
AlexK

Reputation: 3011

Is your original value saved in one variable? If so, you can utilize the scan function in a data step or in Proc SQL to extract the second element from a string that contains comma delimiter:

data want (drop=str rename=(new_str=str));
   set orig;
   length new_str $ 1;
   new_str = strip(scan(str,2,','));
run;

proc sql;
   create table work.want
   as select *, strip(scan(str,2,',')) as new_str length=1
   from orig;
quit;

In Proc SQL, if you want to replace the original column with the updated column, you can replace the * in the SELECT clause with the names of all columns other than original variable you are modifying.

Upvotes: 2

Related Questions