BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

6 REPLIES 6
FriedEgg
SAS Employee

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

WoolieKyat
SAS Employee

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;

Tom
Super User Tom
Super User

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.

WoolieKyat
SAS Employee

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

WoolieKyat
SAS Employee

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;

Ksharp
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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