DATA Step, Macro, Functions and more

Binary variable...

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Binary variable...

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_IDItem_IDXXX
2129135577-RE0
2128885877-MA1
21288897801
212790855-140-CP0
2127905877-MA0

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


Accepted Solutions
Solution
‎12-01-2014 09:26 AM
PROC Star
Posts: 7,363

Re: Binary variable...

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.

View solution in original post


All Replies
PROC Star
Posts: 7,363

Re: Binary variable...

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;

Occasional Contributor
Posts: 7

Re: Binary variable...

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..

Respected Advisor
Posts: 3,892

Re: Binary variable...

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.

PROC Star
Posts: 7,363

Re: Binary variable...

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;

Occasional Contributor
Posts: 7

Re: Binary variable...

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

Data.PNG

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

;

Occasional Contributor
Posts: 7

Re: Binary variable...

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

Solution
‎12-01-2014 09:26 AM
PROC Star
Posts: 7,363

Re: Binary variable...

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.

Occasional Contributor
Posts: 7

Re: Binary variable...

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

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

Super Contributor
Posts: 578

Re: Binary variable...

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 354 views
  • 3 likes
  • 4 in conversation