topic Single record from multiple records while keeping flags in SAS Enterprise Guide

Single record from multiple records while keeping flags

lawpaw — Wed, 09 Aug 2023 13:27:09 GMT

I have a table that provides multiple customer records with multiple flags (1,0) for product items, however only 1 flag is present per record. I'd like to create a single record with the customer id and all of the product flags

Have:

Customer ID	Bananas	Apples	Cherries	Oranges
123	1	0	0	0
123	0	1	0	0
123	0	0	0	1

Want:

123

Re: Single record from multiple records while keeping flags

PaigeMiller — Wed, 09 Aug 2023 13:30:00 GMT

proc summary data=have nway;
    class customerID;
    var bananas apples cherries oranges;
    output out=want max=;
run;

Re: Single record from multiple records while keeping flags

ballardw — Wed, 09 Aug 2023 15:09:16 GMT

May want to learn to search the forum.

I think we get this, or a very similar question, about every 4 months (possibly related to some professor's teaching schedule)

I will say that your desired result does not show "all the flags" because it doesn't show the 0 value "flags". Rephrasing to "I want the maximum value as the 1 valued flags indicates something of interest and 0 otherwise" is more precise and leads to coding solutions like @PaigeMiller

You also don't need to create a data set to get than information. The Procedures Report and Tabulate will show that result.

proc tabulate data=have;
   class customerid;
   var apples bananas cherries oranges;
   table customerid,
         (apples bananas cherries oranges)*max=''
         ;
run;

Proc report data=have;
   columns customerid apples bananas cherries oranges;
   define customerid/group;
   define apples/ max;
   define bananas / max;
   define cherries/ max;
   define oranges / max;
run;

For some things you may even find in handier to reshape the data so that you have a variable "Fruit" that has values like "Apple" "Banana" "Cherry" and "Orange".