topic Re: Binary variable... in SAS Programming

Binary variable...

DDaugaard — Sun, 30 Nov 2014 15:07:59 GMT

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

Re: Binary variable...

art297 — Sun, 30 Nov 2014 16:10:12 GMT

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;

Re: Binary variable...

DDaugaard — Sun, 30 Nov 2014 20:04:09 GMT

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

Re: Binary variable...

Patrick — Sun, 30 Nov 2014 20:54:00 GMT

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.

Re: Binary variable...

art297 — Sun, 30 Nov 2014 22:32:51 GMT

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;

Re: Binary variable...

DDaugaard — Mon, 01 Dec 2014 12:34:25 GMT

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

;

Re: Binary variable...

DDaugaard — Mon, 01 Dec 2014 12:40:55 GMT

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

Re: Binary variable...

DBailey — Mon, 01 Dec 2014 14:26:17 GMT

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;

Re: Binary variable...

art297 — Mon, 01 Dec 2014 14:26:55 GMT

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.

Re: Binary variable...

DDaugaard — Mon, 01 Dec 2014 14:54:43 GMT

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!