topic Re: Cutting & deleting characters from string in an array in SAS Programming

Cutting & deleting characters from string in an array

sasseln — Thu, 19 Jan 2017 14:32:50 GMT

Hey all,

I am trying for a few days already to cut the last 8 digits from a string into a new variable and delete theses digits from the old one. It needs to be done over 100 variables, so it has to be within an array.

The original variables look like that:

1234b8:r:20060607

627::20071012

382027:l:20071212

182b::20071615

17293b:r:20081281

There are 100 of these kind.

I tried scan and substr, that works for a single variable, but somehow within the array, the values with a character between the :: get lost.

The result should look like that:

Var Date

1234b8:r 20060607

382027:l 20071212

182b 20071615

17293b:r 20081281

Probably there is an easy way to do that, but I can't find it.

Thank you in advance

All the best!

Re: Cutting & deleting characters from string in an array

RW9 — Thu, 19 Jan 2017 15:16:39 GMT

Something like:

data want;
  length thevar var date $50;
  thevar="1234b8:r:20060607";
  date=substr(thevar,lengthn(thevar)-7,10);
  var=tranwrd(thevar,strip(date),"");
run;

Re: Cutting & deleting characters from string in an array

Kurt_Bremser — Thu, 19 Jan 2017 15:23:13 GMT

Try this:

data have;
input string : $20.;
cards;
1234b8:r:20060607
627::20071012
382027:l:20071212
182b::20071615
17293b:r:20081281
;
run;

data want;
set have;
length datestr $8;
format date yymmdd10.;
datestr = scan(string,-1,':','m');
date = input(datestr,yymmdd8.);
string = catx(':',scan(string,1,':','m'),scan(string,2,':','m'));
run;

proc print data=want noobs;
run;

The result:

string      datestr           date

1234b8:r    20060607    2006-06-07
627         20071012    2007-10-12
382027:l    20071212    2007-12-12
182b        20071615             .
17293b:r    20081281             .

Note that you have some invalid dates.

Re: Cutting & deleting characters from string in an array

art297 — Thu, 19 Jan 2017 15:34:05 GMT

@RW9's code will result in a missing value for var if the first and second strings are the same.

The following is a slight modification of his code that corrects for that:

data have;
  length thevar $50;
  input;
  thevar=_infile_;
  cards;
1234b8:r:20060607
627::20071012
382027:l:20071212
182b::20071615
17293b:r:20081281
2008128120081281
;

data want;
  set have;
  length var date $50;
  date=substr(thevar,lengthn(thevar)-7,10);
  var=substr(thevar,1,lengthn(thevar)-8);
run;

HTH,

Art, CEO, AnalystFinder.com

Re: Cutting & deleting characters from string in an array

Astounding — Thu, 19 Jan 2017 15:37:22 GMT

If you can do it for one variable accurately, using arrays shouldn't change the outcome. One example:

data want;

set have;

array original {100} original_1 - original_100;

array dates {100} $ 8 date_1 - date_100;

do _n_=1 to 100;

dates{_n_} = scan(original{_n_}, -1, ':');

original{_n_} = substr(original{_n_}, 1, length(original{_n_})-9);

if substr(original{_n_}, length(original{_n_}), 1) = ':' then substr(original{_n_}, length(original{_n_}), 1) = ' ';

end;

run;

It's untested, but I think I got the parentheses in the proper place.

Re: Cutting & deleting characters from string in an array

DanielSantos — Thu, 19 Jan 2017 15:40:59 GMT

Hi.

Another variation.

data want;
S1='1234b8:r:20060607';
S2=substr(S1,lengthn(S1)-7);
substr(S1,lengthn(S1)-7)='';
run;

Using the substr function on left of =

More on this here: https://v8doc.sas.com/sashtml/lgref/z0212267.htm

Daniel Santos @ www.cgd.pt

Re: Cutting & deleting characters from string in an array

mansour_ibrahim — Thu, 19 Jan 2017 16:34:46 GMT

Good morning,

I propose this solution

data test;
input var $50.;
cards;
1234b8:r:20060607
627::20071012
382027:l:20071212
182b::20070615
17293b:r:20081231
;run;


DATA test1 (keep=var: date);
format date yymmdd10.;
set test end=eof;
   length code $10.;
   IF _N_=1 THEN RE = PRXPARSE("/\:\d{8}/");
   retain RE;
   CALL PRXSUBSTR(RE,var,START,LENGTH); 
   IF START GT 0 THEN DO;    
   code = SUBSTRN(var,START + 1,LENGTH - 1); 
   date =input (code,yymmdd10.);
   var2=SUBSTRN(var,1,LENGTH(var) - LENGTH);
   output;
   end; 
run ;

Re: Cutting & deleting characters from string in an array

sasseln — Fri, 20 Jan 2017 14:54:39 GMT

Thank you all!

But all of the codes make my log file running full when they are used within an array.

Why is that and is there a solution for it?

@mansour_ibrahim

Somehow it works fine for the first variable, but when used in an array, in the second variable the date sometimes isn't cut off.

Do you know why?

ops_ko{i} is the variable.

DATA testila;
set test;

array ops_ko ops_ko1-ops_ko100;
array dat_ops dat_ops1-dat_ops100;
do i=1 to 100;

   if _N_=1 then RE = PRXPARSE("/\:\d{8}/");
   retain RE;
   call PRXSUBSTR(RE,ops_ko{i},START,LENGTH);
   if START GT 0 then do;
   dat_ops{i} = SUBSTRN(ops_ko{i},START + 1,LENGTH - 1);
   ops_ko{i} = SUBSTRN(ops_ko{i},1,LENGTH(ops_ko{i}) - 9);
   output;
   end;

end;
drop i Start Length RE;
run;

Re: Cutting & deleting characters from string in an array

RW9 — Fri, 20 Jan 2017 15:06:44 GMT

Then your doing something other than the suggestions posted here. My datastep above should be printed to the log, followed by three other text rows with note, and two rows of time taken to run. Nothing more. Without seeing your log/code we can't tell. Is it your setup, are you running other things, are you printing something to log etc?

Re: Cutting & deleting characters from string in an array

sasseln — Fri, 20 Jan 2017 15:11:38 GMT

Thanks for the fast reply!

I am only running this code:

data test1;
set test;

array ops_ko ops_ko1-ops_ko100;
array dat_ops dat_ops1-dat_ops100;
do i=1 to 100;

dat_ops{i}=substr(ops_ko{i},lengthn(ops_ko{i})-7,10);
ops_ko{i}=tranwrd(ops_ko{i},strip(dat_ops{i}),"");
end;

run;

Thank you!

Re: Cutting & deleting characters from string in an array

mansour_ibrahim — Fri, 20 Jan 2017 15:33:09 GMT

data test;
input  @1 var $17. @18 var1 $20.;
cards;
1234b8:r:20060607 1234b8:r:20060607
152::20071012     627::20091012
382027:l:20071212 382027:l:20051212
182b::20070615    182b::20010615
17293b:r:20081231 17293b:r:20001231
;run;

DATA test1;
format date date1 yymmdd10.; 
set test;
array vr (*) $ var var1; 
array cd (*) $ code code1;
array dt(*) $ date date1;
array pr (*) $ prt1 prt2;  
do i=1 to dim(vr);
   if _N_=1 then RE = PRXPARSE("/\:\d{8}/");             
   retain RE;                                            
   call PRXSUBSTR(RE,vr(i),START,LENGTH);              
   if START GT 0 then do;                                
   cd{i} = SUBSTRN(vr{i},START + 1,LENGTH - 1);
   dt(i) =input (cd(i),yymmdd10.);
   pr(i)=SUBSTRN(vr(i),1,LENGTH(vr(i)) - LENGTH);
      
   end;
end;
drop i Start Length RE;
run;

Re: Cutting & deleting characters from string in an array

ballardw — Fri, 20 Jan 2017 15:54:30 GMT

What is the rule that excludes data from the row with 627::20071012?

Re: Cutting & deleting characters from string in an array

mansour_ibrahim — Fri, 20 Jan 2017 16:31:52 GMT

data test;
input  @1 var $17. @18 var1 $20.;
cards;
1234b8:r:20060607 1234b8:r:20060607
152::20071012     627:r:20091012
382027:l:20071212 382027::20051212
182b::20070615    182b::20010615
17293b:r:20081231 17293b:r:20001231
;run;

DATA test1;
format date date1 yymmdd10.; 
set test;
array vr (*) $ var var1; 
array cd (*) $ code code1;
array dt(*) $ date date1;
array pr (*) $ prt1 prt2;  
do i=1 to dim(vr);
   if _N_=1 then RE = PRXPARSE("/\:\d{8}/"); RX=PRXPARSE("/\d+\:{2}\d{8}/");             
   retain RE RX;
   if prxmatch(RX, vr(i)) = 0 then do;
   call PRXSUBSTR(RE,vr(i),START,LENGTH);              
   if START GT 0 then do;                                
   cd{i} = SUBSTRN(vr{i},START + 1,LENGTH - 1);
   dt(i) =input (cd(i),yymmdd10.);
   pr(i)=SUBSTRN(vr(i),1,LENGTH(vr(i)) - LENGTH);
      
   end;
   end;
end;
drop i Start Length RE RX;
run;

Re: Cutting & deleting characters from string in an array

RW9 — Fri, 20 Jan 2017 17:04:55 GMT

Can you post the log? Are you getting lots of notes or something?

Re: Cutting & deleting characters from string in an array

RW9 — Fri, 20 Jan 2017 17:04:56 GMT

Can you post the log? Are you getting lots of notes or something?

Re: Cutting & deleting characters from string in an array

sasseln — Mon, 23 Jan 2017 09:57:59 GMT

Hi mansour_ibrahim,

thank you for the code! But now it exclusively cuts the data with an l or r in between.

Re: Cutting & deleting characters from string in an array

sasseln — Mon, 23 Jan 2017 10:49:06 GMT

Hi RW9,

this is the code:

data test2;
set test;
array ops_ko ops_ko1-ops_ko100;
array dat_ops dat_ops1-dat_ops100;
do i=1 to 100;
*length ops_ko date $50;
dat_ops{i}=substr(ops_ko{i},lengthn(ops_ko{i})-7,10);
ops_ko{i}=substr(ops_ko{i},1,lengthn(ops_ko{i})-8);
end;
run;

Re: Cutting & deleting characters from string in an array

sasseln — Mon, 23 Jan 2017 10:08:18 GMT

@ballardw

This one in the code fo RW9:

dat_ops{i}=substr(ops_ko{i},lengthn(ops_ko{i})-7,10);

And for mansour_ibrhaim:

if _N_=1 then RE = PRXPARSE("/\:\d{8}/"); RX=PRXPARSE("/\d+\:{2}\d{8}/");
   retain RE RX;
   if prxmatch(RX, ops_ko(i)) = 0 then do;
   call PRXSUBSTR(RE,ops_ko(i),START,LENGTH);
   if START GT 0 then do;
   cd{i} = SUBSTRN(ops_ko{i},START + 1,LENGTH - 1);
   dt(i) =input (cd(i),yymmdd10.);
   pr(i)=SUBSTRN(ops_ko(i),1,LENGTH(ops_ko(i)) - LENGTH);

Re: Cutting & deleting characters from string in an array

RW9 — Mon, 23 Jan 2017 10:08:48 GMT

Sorry, you have actually posted the bit of the log right between important bits. I am specifically looking at notes/errors/warnings. The reason being is this kind of output occurs when you substr invalid data, or input invalid numeric data or something along those lines. What SAS does is print out all the data for that observation where that error occurs, hence as all your data seems to have the same problem, i.e. a logic problem, then you are getting large amounts of output in the log.

It is likely down to your array statements:

array ops_ko ops_ko1-ops_ko100;

These create numeric arrays, and you are trying to put substring information into them. Try chaning to something like:

array ops_ko $200. ops_ko1-ops_ko100;

Also note, this is a very good example of why it is recommended to post test data - in the form of a datastep - so that we can see exactly what you are working with. I do not see what you do, nor can I run what you are running!

Re: Cutting & deleting characters from string in an array

sasseln — Mon, 23 Jan 2017 10:48:18 GMT

It looks like it is as you said a problem with invalid data.

I ran your array but I has the same effect, but I found an important bit in the log:

dat_ops95= dat_ops96= dat_ops97= dat_ops98= dat_ops99= dat_ops100= i=101 _ERROR_=1 _N_=1
NOTE: Ungültiges zweites Argument für Funktion SUBSTR in Zeile 2371 Spalte 14. --> translated: Invalid second argument for the function substr

NOTE: Over 100 NOTES, additional NOTES suppressed.
NOTE: Ungültiges zweites Argument für Funktion SUBSTR in Zeile 2371 Spalte 14. --> translated: Invalid second argument for the function substr

The data look like this, for the variables ops_ko1 till ops_ko100 and fromatted as $CHAR19. :

1234b8:r:20060607

627::20071012

382027:l:20071212

182b::20071615

17293b:r:20081281

Thank you very much!