Binary variable...

[DDaugaard]

Hi, (again).

I'm having some trouble with SAS, and since I don't know what I'm looking for searching for a solution seems impossible.

I tried to illustrate some of the information available for a better understanding:

Order_ID	Item_ID	XXX
212913	5577-RE	0
212888	5877-MA	1
212888	9780	1
212790	855-140-CP	0
212790	5877-MA	0

I would like to search for the specific Item_ID (9780), and whenever this Item_ID appears go to Order_ID list and look for all products attachted to this order. Further this information should be used to create a binary variable (Illustrated by the XXX) telling me if the Item_ID is a part of an order with the 9780 Item_ID.

To sum up I would like a binary variable telling me if a product is a part of an order containing Item_ID 9780.

Any help is much appreciated!

Thanks

[art297]

You can do it fairly easily using proc sql. e.g.:

proc sql;

  create table want as

    select *,

    case when count(item_id-'0780') ge 1 then 1

         else 0 end as XXX

      from have

        group by order_id

  ;

quit;

[DDaugaard]

Somehow that didn't quite do it. Might have done something wrong.

I changed:

                         case when count(item_id-'0780') ge 1 then 1

          to:

                         case when count(item_id='0780') ge 1 then 1 (the "=" after item_ID).

Before i changed it, it was subtracting 0780 from all item_ID, which resulted in an error.

Now that I have changed it, I'm getting the new XXX variable but it's counting 1 in every single observation..

[Patrick]

If Art's code doesn't return what you want then please post some representative sample data (a data step creating such data) and then show us exactly how the desired result should look like (if different to what you've already posted).

This way we will be able to provide you code which is tested against your actual data structure.

[art297]

Sorry, the code I posted had two errors. Try this one:

data have;

  informat item_id $10.;

  infile cards dlm='09'x;

  input Order_ID Item_ID;

  cards;

212913 5577-RE

212888 5877-MA

212888 9780

212790 855-140-CP

212790 5877-MA

;

proc sql;

  create table want as

    select *,

    case when sum(strip(item_id)='9780') ge 1 then 1

         else 0 end as XXX

      from have

        group by order_id

  ;

quit;

[DDaugaard]

Sorry if I am being a bit unclear in my actual needs.

I took a screenshot of the actual data. Some of the variables are in Danish, but I will give a translation to each of them  

                   Order_ID             Item_ID / Order date / shipping date               Supplier          Price          return               N

Thanks for your help Arthur, but in the actual data set we have 800.000 observations, can I somehow use the code you mentioned above for the entire data set?

Im thinking of the part you mention after "Card;", this will be impossible to do for 800.000 observations. (I tried to highlight it with bold)

data have;

  informat item_id $10.;

  infile cards dlm='09'x;

  input Order_ID Item_ID;

  cards;

212913 5577-RE

212888 5877-MA

212888 9780

212790 855-140-CP

212790 5877-MA

;

[DDaugaard]

What I would like to do is to look for "9780" for the varenr(Item_ID) variable, and whenever this specific code appear I would like it to go to the Ordrennr_(Order_ID) look for how many products are a part of this Order_ID and mark those with a 1, otherwise I would like the rest to have a 0, telling me that they are not a part of an order with the Item_ID 9780

[art297 · Accepted solution]

What form is the data in? The entire datastep (with the cards; statement) was only to provide some data for testing.

Is your file an excel workbook, a text file, a SAS dataset, or what?

The proc sql step statement "from have" is the only line that might have to be changed once your data is in the form of a SAS dataset.

[DDaugaard]

It's a SAS dataset. The proc sql step you supplied seems to do the job

Thanks a lot for your help, it's much appreciated!

[DBailey]

proc sql;

select

     t1.*,

     case when t2.order_id is null then 0 else 1 end as xxx

from

     have t1

     left outer join (select order_id from have where item_id='9780') t2

     on t1.order_id=t2.order_id;

quit;