expanding all possible combinations of possibilities per row.

Accepted Solution Solved
Reply
SAS Employee
Posts: 7
Accepted Solution

expanding all possible combinations of possibilities per row.

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.


Accepted Solutions
Solution
‎02-01-2015 02:17 PM
Super User
Super User
Posts: 6,309

Re: expanding all possible combinations of possibilities per row.

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;

  end;

  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.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,300

Re: expanding all possible combinations of possibilities per row.

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

SAS Employee
Posts: 7

Re: expanding all possible combinations of possibilities per row.

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;

Solution
‎02-01-2015 02:17 PM
Super User
Super User
Posts: 6,309

Re: expanding all possible combinations of possibilities per row.

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;

  end;

  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.

SAS Employee
Posts: 7

Re: expanding all possible combinations of possibilities per row.

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

SAS Employee
Posts: 7

Re: expanding all possible combinations of possibilities per row.

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;

Grand Advisor
Posts: 9,567

Re: expanding all possible combinations of possibilities per row.

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 271 views
  • 1 like
  • 4 in conversation