BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DDaugaard
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

9 REPLIES 9
art297
Opal | Level 21

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
Calcite | Level 5

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
Opal | Level 21

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
Opal | Level 21

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
Calcite | Level 5

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

;

DDaugaard
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5

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!

DBailey
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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