topic Delete first word before first space in SAS Programming

Delete first word before first space

skavli — Wed, 21 Oct 2020 08:58:12 GMT

Hi y'all i hope you're doing fine,

I'm working on a cars database and i'm having a little hard time extract name and surname of cars, my table looks like this :

Brand

CITROEN C3 AIRCROSS

DS3 CROSSBACK E-TENSE

OPEL GRANDLAND X

Now i wanna create two variables one named Make ( for the brand name ) and the other Model (for the brand model), so far so good for the Make ( because it's the first string before the space ) so with this i've succeeded getting my Make

Make = scan(brand,1,' ');

Now it's a little bit harder to for the model i've had few ideas but none was effective, for my model variable i want everything after the first space, i thought first to ease the process use tranwrd function and replace all my spaces with a underscore but it replaces also the space in the end with underscore, for the substr function i need the position for each observation wich a little bit hard since each observation is different.

Do you have any hint for me to make this work and all the strings after the first space please ?

Re: Delete first word before first space

Kurt_Bremser — Wed, 21 Oct 2020 09:09:18 GMT

Use CALL SCAN and then SUBSTR:

data have;
input brand $50.;
datalines;
CITROEN C3 AIRCROSS
DS3 CROSSBACK E-TENSE
OPEL GRANDLAND X
;

data want;
set have;
make = scan(brand,1);
call scan(brand,1,p,l);
model = substr(brand,l+2);
drop p l;
run;

Re: Delete first word before first space

skavli — Wed, 21 Oct 2020 09:12:14 GMT

Thanks that works perfectly, i gotta go see what that call function do and remember it.
Greetings sir

Re: Delete first word before first space

andreas_lds — Wed, 21 Oct 2020 09:19:40 GMT

Try

model = substr(brand, length(make)+2);

You could, of course, use a regular expression:

data want;
   set have;
   
   length rx 8 make model $ 50;
   retain rx;
   drop rx;
   
   if _n_ = 1 then do;
      rx = prxparse('/(\S+) (.+)/');
   end;
   
   if prxmatch(rx, brand) then do;
      make = prxposn(rx, 1, brand);
      model = prxposn(rx, 2, brand);
   end;
run;

Re: Delete first word before first space

ScottBass — Wed, 21 Oct 2020 09:21:43 GMT

@skavli wrote:

Hi y'all i hope you're doing fine,

I'm working on a cars database and i'm having a little hard time extract name and surname of cars, my table looks like this :

Brand

CITROEN C3 AIRCROSS

DS3 CROSSBACK E-TENSE

OPEL GRANDLAND X

Now i wanna create two variables one named Make ( for the brand name ) and the other Model (for the brand model), so far so good for the Make ( because it's the first string before the space ) so with this i've succeeded getting my Make
Make = scan(brand,1,' ');
Now it's a little bit harder to for the model i've had few ideas but none was effective, for my model variable i want everything after the first space, i thought first to ease the process use tranwrd function and replace all my spaces with a underscore but it replaces also the space in the end with underscore, for the substr function i need the position for each observation wich a little bit hard since each observation is different.

Do you have any hint for me to make this work and all the strings after the first space please ?

Edit your original post, using a SAS datastep with datalines statements to create your sample dataset. Don't paste in a screenshot since if forces *us* to create *your* sample dataset. Post using the "Insert SAS code" icon.

data have;
   length somevariable $200;
   somevariable='foo    bar blah';
run;

data want1;
   set have;
   length make $100 model $100;
   pos=index(somevariable,' ');
   if pos then do;
      make=substr(somevariable,1,pos);
      model=substr(somevariable,pos+1);
      model=strip(model);
   end;
run;

data want2;
   set have;
   length make $100 model $100;
   rx=prxparse('/(.*?) (.*)/o');
   if prxmatch(rx,somevariable) then do;
      make=prxposn(rx,1,somevariable);
      model=prxposn(rx,2,somevariable);
   end;
run;