turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Need help on how to iterate through a list of item...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-27-2017 06:28 AM

HI All,

I have a dataset which has sold items in seperate lines against the same order number. I want to write a script which will look for "bundles" or groups of items and then create a variable with a 1 in it.

For example

got

Order no.-Item -Bundle A

12312 01

12312 02

In the above I would want bundle A to be 1 based on the criteria that an order contains a certain combination of items.

I know there is a way to transpose the data but I'm sure you can loop the dataset instead?

Any ideas?

Thanks guys

Accepted Solutions

Solution

10-30-2017
05:43 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JordanWillis

10-30-2017 05:11 AM

I have two suggestions, one with a proc sql and a subquery, the other with proc freq and a datastep merge back:

```
data have;
infile datalines dlm=',';
input orderid :$5. product :$20.;
datalines;
1234,1
1234,2
1232,5
;
run;
proc sql;
create table want as
select
a.*,
case
when (select count(distinct b.product) from have b where b.orderid = a.orderid) > 1
then '1'
else '0'
end as bundle
from have a
;
quit;
proc freq data=have noprint;
tables orderid /out=want1 (keep=orderid count);
run;
proc sort data=have;
by orderid;
run;
data want2;
merge
have
want1
;
by orderid;
if count > 1
then bundle = '1';
else bundle = '0';
bundle_num = count > 1;
drop count;
run;
```

Unsing a numeric flag variable makes later use in conditions easier (0 or . = false, everything else = true)

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JordanWillis

10-27-2017 07:15 AM

Please post your example data in a way that makes it recognizable as a SAS dataset.

no.-Item and -Bundle both are invalid as SAS names.

So take the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert your dataset to a datastep and post that here according to https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce, "How can I add SAS syntax to a post? "

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

10-30-2017 04:42 AM - edited 10-30-2017 04:50 AM

Hi Kurt,

Thanks for your response. Here's the code of a sample dataset:

```
data have;
input orderid $5. product $20. bundle $2.;
datalines;
1234,1
1234,2
1232,5
;
run;
data want;
input orderid $5. product $20. bundle $2.;
datalines;
1234,1,1
1234,2,1
1232,5,0
;
run;
```

In the above those lines where product = 1 and product = 2 beside the same order number would have bundle = 1?

Does that make sense?

Cheers,

Jordan

Solution

10-30-2017
05:43 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JordanWillis

10-30-2017 05:11 AM

I have two suggestions, one with a proc sql and a subquery, the other with proc freq and a datastep merge back:

```
data have;
infile datalines dlm=',';
input orderid :$5. product :$20.;
datalines;
1234,1
1234,2
1232,5
;
run;
proc sql;
create table want as
select
a.*,
case
when (select count(distinct b.product) from have b where b.orderid = a.orderid) > 1
then '1'
else '0'
end as bundle
from have a
;
quit;
proc freq data=have noprint;
tables orderid /out=want1 (keep=orderid count);
run;
proc sort data=have;
by orderid;
run;
data want2;
merge
have
want1
;
by orderid;
if count > 1
then bundle = '1';
else bundle = '0';
bundle_num = count > 1;
drop count;
run;
```

Unsing a numeric flag variable makes later use in conditions easier (0 or . = false, everything else = true)

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

10-30-2017 05:20 AM

Hi Kurt,

Some great examples there. I'm afraid I'm going to throw in a curve ball; what if my product ID variable is character so we can't work with the numerical logic. For example

```
data have;
input orderid $5. product $20. bundle $2.;
datalines;
1234 1-01
1233 2-02
1232 5
;
run;
data want;
input orderid $5. product $20. bundle $2.;
datalines;
1234 1-01 1
1233 2-02 1
1232 5-01 0
;
run;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JordanWillis

10-30-2017 05:41 AM

Look at the codes again. product was already character, and both methods make no assumptions for data types.

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

10-30-2017 05:43 AM

So it does; All Hail Kurt!

Thanks for your expert help, incredibly appreciated