Dynamically Concatenate Columns

[bendsteel6]

I'm trying to concatenate columns dynamically while adding additional text.  Can someone tell me the best method to go about getting the 'want' dataset (with the added 'text' field) from the 'have' dataset?  There may even be more PN and QTY fields depending on the run.

data have;
infile datalines dlm='|' dsd;
length claim_job_id $20. PN_1-PN_3 $15. QTY_1-QTY_3 $3.;
input claim_job_id PN_1 QTY_1 PN_2 QTY_2 PN_3 QTY_3 ;
datalines;
7698-2720K-233-1|990950|100||||
7698-2720K-282-1|990950|100|GRO836029|100||
7698-2720K-922193-1|990950|100|GRO836029|100|GRO8399999|50
;
run;

data want;
infile datalines dlm='|' dsd;
length claim_job_id $20. PN_1-PN_3 $15. QTY_1-QTY_3 $3. text $500.;
input claim_job_id PN_1 QTY_1 PN_2 QTY_2 PN_3 QTY_3 text;
datalines;
7698-2720K-233-1|990950|100|||||PN 990950 has a pack qty of 100
7698-2720K-282-1|990950|100|GRO836029|100|||PN 990950 has a pack qty of 100, PN GRO836029 has a pack qty of 100
7698-2720K-922193-1|990950|100|GRO836029|100|GRO8399999|50|PN 990950 has a pack qty of 100, PN GRO836029 has a pack qty of 100, PN GRO8399999 has a pack qty of 50
;

run;

[ballardw]

Something like this is one way:

data have;
   infile datalines dlm='|' dsd;
   length claim_job_id $20. PN_1-PN_3 $15. QTY_1-QTY_3 $3.;
   input claim_job_id PN_1 QTY_1 PN_2 QTY_2 PN_3 QTY_3 ;
   array p pn_1-pn_3;
   array q qty_1-qty_3;
   length text $ 500;
   do i=1 to dim(p);
      if not missing(p[i]) then text= catx(', ',text, catx(' ','PN',p[i],'has a pack qty of',q[i]));
   end;

datalines;
7698-2720K-233-1|990950|100||||
7698-2720K-282-1|990950|100|GRO836029|100||
7698-2720K-922193-1|990950|100|GRO836029|100|GRO8399999|50
;
run;

/*PN 990950 has a pack qty of 100, PN GRO836029 has a pack qty of 100*/

Please post code or log entries into a code box opened using the forums </> or "running man" icon.

And please describe why this desirable Generally for most reporting and analysis you would be better off having one record per PN for each claim_id, then you don't have to work around "missing values".

[bendsteel6]

Am I not in the Programming forum?

And I don't see a 'running man" icon.

I don't understand.

[Reeza]

The running man icon is the 7/8th icon in the editor. If you hit Quick Reply you don't get the full editor, but in your initial post you would have. You can click the three dots besides a post under your name to edit a post. 

[ballardw]

---

@bendsteel6 wrote:

Am I not in the Programming forum?

And I don't see a 'running man" icon.

I don't understand.

---

What browser are you using? When you are posting a message there should be a row of icons above the message box.

The reason that you want to use one of the code boxes is that the forum windows here will reformat text and may insert undesirable characters or move things.

Example:

166 data have;
167 infile datalines dlm='|' dsd;
168 length claim_job_id $20. PN_1-PN_3 $15. QTY_1-QTY_3 $3.;
169 input claim_job_id PN_1 QTY_1 PN_2 QTY_2 PN_3 QTY_3 ;
170 array p pn_1-pn_3;
171 array q qty_1-qty_3;
172 length text $ 500;
173 do i=1 to dim(p);
174 if not missing(p[i]) then text= catx(', ',text, catx(' ','PN',p[i],'has a pack qty
174! of',q[i]));
175 end;

but in a code box:

166  data have;
167     infile datalines dlm='|' dsd;
168     length claim_job_id $20. PN_1-PN_3 $15. QTY_1-QTY_3 $3.;
169     input claim_job_id PN_1 QTY_1 PN_2 QTY_2 PN_3 QTY_3 ;
170     array p pn_1-pn_3;
171     array q qty_1-qty_3;
172     length text $ 500;
173     do i=1 to dim(p);
174        if not missing(p[i]) then text= catx(', ',text, catx(' ','PN',p[i],'has a pack qty
174! of',q[i]));
175     end;

Note the indents have been removed. Also the pasted text outside of the box is likely to have acquired extra "line feeds" so that copying it into an Editor is now has lots of extra lines. Both of these behaviors make it hard to use code not posted in code boxes. Sometimes the resulting code will not even run or generates errors.

And we will often ask the same thing regardless of which part of the forum because code is involved in a great many tasks, or to show LOG results especially with errors. SAS often places indicators in the log where an error occurs. But the reformatted text in the message windows moves those indicators meaning that it is harder to tell exactly where the error occurred.

If you don't see the icons then likely  you need something, either in your browser or a different browser entirely.

[bendsteel6]

Thanks everyone for the information on posting.  I now see the icon...I just had to expand the list of icons...I feel stupid,but I learned something new today.

And thanks Reeza for the reply of CATS with arrays.  I will definitely check it out!

[Reeza · Accepted solution]

Use CATT()/CATS()/CAT() and an array to loop through to get what you want. Making sure spaces are in correctly is usually the issue. 

I edited your question to put the code in the code block. 

Here's a tutorial on using Arrays in SAS
https://stats.idre.ucla.edu/sas/seminars/sas-arrays/