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.
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.
The format of your input is not very clear. Please edit your post.
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;
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.
Thank you very much Tom this is exactly what i wanted. I really appreciate your help.
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.