Solved
SAS Employee
Posts: 7

# 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
Posts: 8,120

## Re: expanding all possible combinations of possibilities per row.

Posted in reply to WoolieKyat

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.

All Replies
Trusted Advisor
Posts: 1,318

## Re: expanding all possible combinations of possibilities per row.

Posted in reply to WoolieKyat

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
Posts: 8,120

## Re: expanding all possible combinations of possibilities per row.

Posted in reply to WoolieKyat

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;

Super User
Posts: 10,784

## Re: expanding all possible combinations of possibilities per row.

Posted in reply to WoolieKyat
```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 and locked.

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

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