topic Re: Pulling string after phrase and before special charater. in SAS Programming

Pulling string after phrase and before special charater.

vgriggs — Sun, 09 Jan 2022 18:17:40 GMT

I was given an excel file where someone stored all of the information in a single column (var1). I need to pull information but it will be in random orders. Good thing is the person gave the information and then put a period after it.

Example of Var1:

Type = 2. Size = 4 in x 12 in. Group = ABC grouping.

Group = A and B Holdings. Type = 1.

Group = Mark H and Company.

The variable I need to pull is group. It always starts with "Group = " and has a period in the end. But will be anywhere within the var1 (so you can't name a specific period. Sometimes it may not exist. This variable can be any length in words. I just need to pull the string between "Group = " and the period.

This can't be done in excel due to the size of the dataset.

I have tried scan, find, splitting at the period, and I am not sure what to do at this point to organize it.

Re: Pulling string after phrase and before special charater.

yabwon — Sun, 09 Jan 2022 21:40:44 GMT

Try this:

data have;
infile cards4 dlm = "0A0D"x;
input Var1 : $ 100.;
cards4;
Type = 2. Size = 4 in x 12 in. Group = ABC grouping.
Group = A and B Holdings. Type = 1. 
Group = Mark H and Company. 
;;;;
run;
proc print;
run;

data want;
  set have;

  do _N_ = 1 to countw(Var1,".");
    part = strip(scan(Var1,_N_,".")); drop part;

    if upcase(part) =: "GROUP" then
      do;
        group = strip(substr(part, index(part, "=")+1));
      end;
  end;
run;
proc print;
run;

Bart

Re: Pulling string after phrase and before special charater.

data_null__ — Sun, 09 Jan 2022 22:07:38 GMT

You could read the data using NAMED Input.

data have;
   infile cards4;
   length type size group $48;
   input @;
   _infile_= transtrn(_infile_,' =','=');
   input (_all_)(=);
   list;
   cards4;
Type = 2. Size = 4 in x 12 in. Group = ABC grouping.
Group = A and B Holdings. Type = 1. 
Group = Mark H and Company. 
;;;;
run;
proc print;
run;

Re: Pulling string after phrase and before special charater.

Ksharp — Mon, 10 Jan 2022 12:16:36 GMT

data have;
infile cards4 truncover;
input Var1 $ 100.;
cards4;
Type = 2. Size = 4 in x 12 in. Group = ABC grouping.
Group = A and B Holdings. Type = 1. 
Group = Mark H and Company. 
;;;;
run;

data want;
 set have;
pid=prxparse('/(?<=group)\s*=[^\.]+(?=\.)/io');
call prxsubstr(pid,var1,p,l);
if p then  want=scan(strip(substr(var1,p,l)),1,'=');
drop pid p l;
run;

Re: Pulling string after phrase and before special charater.

vgriggs — Mon, 10 Jan 2022 12:16:09 GMT

Thank you so much! This worked!!!

I have new code to learn about today!