Help with two strings

Reply
Super Contributor
Posts: 418

Help with two strings

Hello everyone. I have a dataset with two variables that are concatenated strings, concatenated with the values "#@".

One string is a list of values, and another is a list of flags. The position of the values and flags within the strings are related. Meaning the 7th value in the first string is associated with the 7th value in the second string.

Is there a simple way to Change the Xth occurance in the flag string, if the Xth occurance in the first string equal a specific value (say "(No Data)".).  Please see my example Have and Want datasets for a concrete example of what I am asking for!

Note that in the want example, the third flag has become a 1 (because the 'datavalues' string has '(No data)' in it!

Thanks for your help, I'm stuck on this one!

data have;
   infile datalines delimiter=',';
   informat flagvalue $8000.;
   informat datavalues $8000.;
   input flagvalue $ datavalues $;
   datalines;                     
0#@0#@0#@0,Conventional#@Purchase#@(No Data)#@131.75
;


data WANT;
   infile datalines delimiter=',';
   informat flagvalue $8000.;
   informat datavalues $8000.;
   input flagvalue $ datavalues $;
   datalines;                     
0#@0#@1#@0,Conventional#@Purchase#@(No Data)#@131.75
;

Respected Advisor
Posts: 3,124

Re: Help with two strings

data WANT;

     infile datalines delimiter=',';

     informat flagvalue $8000.;

     informat datavalues $8000.;

     input flagvalue $ datavalues $;

     _n_=count(substr(datavalues,1,find(datavalues,'(No Data)')), '#@');

     substr(flagvalue,ifn(_n_=0,1,(_n_)*3+1),1)='1';

     datalines;

0#@0#@0#@0,Conventional#@Purchase#@(No Data)#@131.75

0#@0#@0#@0,(No Data)#@Purchase#@Conventional#@131.75

0#@0#@0#@0,Conventional#@(No Data)#@Conventional#@131.75

0#@0#@0#@0,Conventional#@Purchase#@(No Data)#@Conventional

;

Good Luck,

Haikuo

Super Contributor
Posts: 418

Re: Help with two strings

Hi Hai.kuo.

That is an awesome answer and thanks for your help! The only Issue I have (and this is 100% my fault) is that one string can have Multiple values of "(No Data)", and I need ALL of the flag fields updated. I should have made that more clear in my example.

So I could have

data WANT;

     infile datalines delimiter=',';

     informat flagvalue $8000.;

     informat datavalues $8000.;

     input flagvalue $ datavalues $;

     datalines;

0#@0#@0#@0,Conventional#@Purchase#@(No Data)#@131.75

0#@0#@0#@0,(No Data)#@Purchase#@Conventional#@131.75

0#@0#@0#@0,Conventional#@(No Data)#@Conventional#@131.75

0#@0#@0#@0,Conventional#@Purchase#@(No Data)#@(No Data)

;

as my base data, and in the last example Both the third and fourth flag would need to be 1.

Thanks again and sorry!

Respected Advisor
Posts: 3,124

Re: Help with two strings

Ok, I feel there is something left to be desired regarding the efficiency, but for now this can get you going:

data WANT;

     infile datalines delimiter=',';

     informat flagvalue $8000.;

     informat datavalues $8000.;

     input flagvalue $ datavalues $;

     array _dv(4) $ 1000;

     array _flg(4);

     do i=1 to 4;

           _dv(i)=scan(datavalues,i,'#@');

           _flg(i)=ifn(_dv(i)='(No Data)',1,0);

     end;

     do i=1 to 4;

           if _flg(i)=1 then

                substr(flagvalue,i*3-2,1)='1';

     end;

     drop i _:;

     datalines;

0#@0#@0#@0,Conventional#@Purchase#@(No Data)#@(No Data)

0#@0#@0#@0,(No Data)#@Purchase#@Conventional#@131.75

0#@0#@0#@0,(No Data)#@(No Data)#@(No Data)#@131.75

0#@0#@0#@0,Conventional#@Purchase#@131.75#@(No Data)

;

Good Luck,

Haikuo

Super User
Posts: 9,681

Re: Help with two strings

data have;
   infile datalines delimiter=','; 
   informat flagvalue $8000.;
   informat datavalues $8000.;
   input flagvalue $ datavalues $;
   datalines;                      
0#@0#@0#@0,Conventional#@Purchase#@(No Data)#@131.75
;
data temp(keep=obs a b);
 set have;
obs+1;
do i=1 to countw(flagvalue,'#@');
 a=scan(flagvalue,i,'#@'); b=scan(datavalues,i,'#@'); 
 if b='(No Data)' then a='1';output;
end;
run;
data want(keep=f d);
 set temp;
 by obs;
 length f d $ 100;
 retain f d;
 f=catx('#@',f,a);
 d=catx('#@',d,b);
 if last.obs then do;output;call missing(f,d);end;
run;


Xia Keshan

Ask a Question
Discussion stats
  • 4 replies
  • 198 views
  • 0 likes
  • 3 in conversation