topic Re: expanding all possible combinations of possibilities per row. in SAS Programming

expanding all possible combinations of possibilities per row.

WoolieKyat — Sun, 01 Feb 2015 18:08:27 GMT

Hey SAS Community

I am having a coding problem I'm struggling with. I have this data set where I am trying to
expand all possible combinations of possibilities per row. The challenges I am having is that not all
columns per observation are populated so the looping techniques that I have
been trying are not give me the results I am anticipating. Below is a sample of
the data set and the results i am trying to achieve.

I have this dataset
tempID                         tempID1                                                                                     tempID3
AL8
                                   20141140||20131140||20121140||20141120||20111140            2014314214
GA109||KS32             20140551||20150552

I want this dataset
ID1_f          ID2_f            ID3_f
AL8
               20141140        2014314214
               20131140        2014314214
               20121140        2014314214
               20141120        2014314214
               20111140        2014314214

GA109   20140551
GA109   20150552
KS32     20140551
KS32     20150552

Any help with this problem would be greatly appreicated.

Re: expanding all possible combinations of possibilities per row.

FriedEgg — Sun, 01 Feb 2015 18:37:13 GMT

The format of your input is not very clear. Please edit your post.

Re: expanding all possible combinations of possibilities per row.

Tom — Sun, 01 Feb 2015 19:17:18 GMT

Probably something like this.

data have ;

length row 8 tempID1-tempID3 $200 ;

infile cards dsd truncover ;

input tempID1-tempID3;

row+1;

cards;

AL8,,

,20141140||20131140||20121140||20141120||20111140,2014314214

GA109||KS32,,20140551||20150552

;;;;

data want ;

set have ;

length id1-id3 $20 ;

do i=1 by 1 until (scan(tempid1,i,'|')=' ');

do j=1 by 1 until (scan(tempid2,j,'|')=' ');

do k=1 by 1 until (scan(tempid3,k,'|')=' ');

id1=scan(tempid1,i,'|');

id2=scan(tempid2,j,'|');

id3=scan(tempid3,k,'|');

if sum(i,j,k)=3 or cmiss(of id1-id3) < 2 then

output;

end;

run;

data _null_;

set want ;

file log dsd ;

put row id1-id3 ;

run;

1,AL8,,

2,,20141140,2014314214

2,,20131140,2014314214

2,,20121140,2014314214

2,,20141120,2014314214

2,,20111140,2014314214

3,GA109,,20140551

3,GA109,,20150552

3,KS32,,20140551

3,KS32,,20150552

NOTE: There were 10 observations read from the data set WORK.WANT.

Re: expanding all possible combinations of possibilities per row.

WoolieKyat — Sun, 01 Feb 2015 19:21:09 GMT

Sorry for the confusion. Here is what the input data set looks like.

data have;

input tempID1$ tempID2$ tempID3 $ dlm=',';

datalines;

AL8 ,,

, 20141140||20131140||20121140||20141120||20111140 , 2014314214

GA109||KS32 , 20140551||20150552

;

run;

Re: expanding all possible combinations of possibilities per row.

WoolieKyat — Sun, 01 Feb 2015 19:49:11 GMT

Thank you very much Tom this is exactly what i wanted. I really appreciate your help.

Re: expanding all possible combinations of possibilities per row.

WoolieKyat — Sun, 01 Feb 2015 20:00:27 GMT

This is one of the looping techniques I was trying that was failing.

data test3;
set test2;
i=1;
j=1;
k=1;
do while(scan(strip(tempID1),i,'||') ne ' ');
do while(scan(strip(tempID2),j,'||') ne ' ');
   do while(scan(strip(tempID3),k,'||') ne ' ');
     ID1_f=scan(tempID,i,'|');
     ID2_f=scan(tempID,j,'|');
     ID3_f=scan(tempID,k,'|');
     put i= j= k=;

     output;

        k=k+1;
      end;
j=j+1;
     end;
i=i+1;
end;
drop i j k;
run;

Re: expanding all possible combinations of possibilities per row.

Ksharp — Mon, 02 Feb 2015 06:34:41 GMT

data have ;
length tempID1-tempID3 $200 ;
infile cards dsd truncover ;
input tempID1-tempID3;
cards;
AL8,, 
,20141140||20131140||20121140||20141120||20111140,2014314214
GA109||KS32,,20140551||20150552
;;;;
run;
data want;
 set have;
 do i=1 to countw(tempID1,'|');
  ID1=scan(tempID1,i,'|');
  do j=1 to  countw(tempID2,'|');
  ID2=scan(tempID2,j,'|');
     do k=1 to  countw(tempID3,'|');
      ID3=scan(tempID3,k,'|');output;
      end;
  end;
 end;
 keep id1 id2 id3;
run;

Xia Keshan