topic Re: How to take values from columns and add to a single row in SAS in SAS Programming

How to take values from columns and add to a single row in SAS

Newbie_23 — Wed, 26 Jul 2023 17:06:55 GMT

Hello,

I have a data set as shown below

Product_ID Type Description

12 A Made of wood

12 B Made of wood

25 C Made of steel

I need to get a dataset like the below, basically the values from Type column is added to the description within paranthesis

Product_ID Description

12 Made of wood ( A,B)

Re: How to take values from columns and add to a single row in SAS

Reeza — Wed, 26 Jul 2023 17:33:35 GMT

Can the description be different if the type is different? How is that case handled?

Re: How to take values from columns and add to a single row in SAS

Newbie_23 — Wed, 26 Jul 2023 17:37:28 GMT

No, the description is the driver for the values to be added to the row. Please note that the description is the same for Type A and B.

Re: How to take values from columns and add to a single row in SAS

PaigeMiller — Wed, 26 Jul 2023 17:57:19 GMT

Your desired output is a poor arrangement of the data in a data set, and makes further programming more difficult. Leave the data in the data set as is, don't try to re-arrange it. (Unless you want a report that looks something like what you showed, that's different).

Re: How to take values from columns and add to a single row in SAS

Reeza — Wed, 26 Jul 2023 18:00:34 GMT

data have;
infile cards dlm=',';
informat product_Id $8. type $8. Description $20.;
input Product_ID    Type      Description;
cards;
12, A, Made of wood
12, B, Made of wood
25, C, Made of steel
;;

proc sort data=have;
by product_id description type;
run;

data want;
set have;
length type_cat $20.;
by product_id description type;
retain type_cat;
if first.product_id then call missing(type_cat);
type_cat = catx(", ", type_cat, type);

if last.description then do;
description = catt(description, " (", type_cat, ")");
output;
end;

run;

Re: How to take values from columns and add to a single row in SAS

Newbie_23 — Thu, 27 Jul 2023 13:10:15 GMT

Hello Reeza,

Thanks for replying. But the type is added to the row only when description is the same between the products.

Any thoughts?

Re: How to take values from columns and add to a single row in SAS

Reeza — Thu, 27 Jul 2023 15:00:16 GMT

This is why it's a good idea to show your full output.

data want;
set have;
length type_cat $20.;
by product_id description type;
retain type_cat;
if first.product_id then call missing(type_cat);
type_cat = catx(", ", type_cat, type);

if last.description and not first.description then do;
description = catt(description, " (", type_cat, ")");
output;
end;
else if last.description then output;

run;

Re: How to take values from columns and add to a single row in SAS

Newbie_23 — Thu, 27 Jul 2023 22:01:45 GMT

data have;
infile cards dlm=',';
informat product_Id $8. type $8. Description $20.;
input Product_ID Type Description;
cards;
12, A, Made of wood
12, B, Made of wood
25, C, Made of steel
15, D, Made of iron
12, F, Made of paper
;

proc sort data=have;
by product_id description type;
run;

data want;
set have;
length type_cat $20.;
by product_id description type;
retain type_cat;
if first.product_id then call missing(type_cat);
type_cat = catx(", ", type_cat, type);

if last.description then do;
description = catt(description, " (", type_cat, ")");
output;
end;

run;

I'm expecting output to be like shown below, please help on what am i missing.Please note that the type is added to the description within paranthesis only when description is the same between the products
I'm expecting output to be like shown below
Product_ID description
12 Made of paper(F)
12 Made of Wood(A,B)
25 Made of steel (C)
15 Made of iron ( D)

Re: How to take values from columns and add to a single row in SAS

Newbie_23 — Fri, 28 Jul 2023 13:48:34 GMT

data want;
set have;
by product_id description NOTSORTED; /* <-------- */

length types $20.;
retain types;

if first.description then call missing(types);
types = catx(", ", types, type);

if last.description then do;
description = catt(description, " (", types, ")");
output;
end;
keep product_id description;
run;

Re: How to take values from columns and add to a single row in SAS

Newbie_23 — Fri, 28 Jul 2023 13:50:28 GMT

Thanks so much Reeza, you guided me in the right direction. 🙂