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

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

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

All Replies

- Mark as New
- Bookmark
- Subscribe
- 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

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- 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
- 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

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- 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
- 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

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- 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