Creating Multiple Observations from an Observation

[DavidLie]

Hi SAS Community,

I would like to ask how to create multiple copy of observations from a single observation.

At the moment, I have the following:

WEEK	ITEM	STORE	PRICE
1166	23	100	1.50
1166	23	200	2.50
1166	23	250	2.50
1166	25	110	3.25
1166	25	120	4.50

I want to have the following:

WEEK	ITEM	STORE	STORE1	PRICE
1166	23	100	100	1.50
1166	23	200	100	2.50
1166	23	250	100	2.50
1166	23	100	200	1.50
1166	23	200	200	2.50
1166	23	250	200	2.50
1166	23	100	250	1.50
1166	23	200	250	2.50
1166	23	250	250	2.50
…	…	…	…	…
1166	25	110	110	3.25
1166	25	120	110	4.50
1166	25	110	120	3.25
1166	25	120	120	4.50

This analysis is in terms of WEEK and ITEM. In the first case, there are 3 observations in WEEK 1166 and ITEM 23. Hence, I want to copy the exact observation by store.

Let me know if this makes sense.

Best,
David

[PaigeMiller]

I don't understand the logic by which you go from your original data set to the data set you want. Can you explain it in detail? Thanks!

Please post data following these instructions so we have an actual SAS data set to work with: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[DavidLie]

Hi Paige,

Thank you for your email. In this case, I want to create exact copies of my current observation by ITEM and WEEK. For example, in the WEEK 1166 and ITEM 23, there are 3 stores (namely Store 100, Store 200 and Store 250).

I want to create exact entries of these 3 stores to capture the interactions. For example:

Store 100 and Store 100;

Store 100 and Store 200;

Store 100 and Store 250:

Store 200 and Store 100;

...

Store 250 and Store 250.

As a result, I have to inflate my dataset of having the same observations from 3 to 9.

WEEK	ITEM	STORE	STORE1	PRICE
1166	23	100	100	1.50
1166	23	200	100	2.50
1166	23	250	100	2.50
1166	23	100	200	1.50
1166	23	200	200	2.50
1166	23	250	200	2.50
1166	23	100	250	1.50
1166	23	200	250	2.50
1166	23	250	250	2.50

Hope I make myself clearer now.

Best,
David

[PeterClemmensen]

I think this is what you want..

data have;
input week item store price;
datalines;
1166 23 100 1.50 
1166 23 200 2.50 
1166 23 250 2.50 
1166 25 110 3.25 
1166 25 120 4.50 
;

data want(drop=i j);
    do _N_=1 by 1 until (last.item);
        set have;
        by item;
    end;
    do i=1 to _N_;
        do j=1 to _N_;
            set have point=j;
            output;
        end;
    end;
run;

[DavidLie]

Hi Draycut,

Thank you for your message. Yes, this is close to something that I want. But:

1. When I tried it in my SAS, item 25 is somehow replaced by item 23.

2. I need to have an entry of STORE1 as well.

I wonder if that can be doable?

Thanks in advance.

Best,
David

[PeterClemmensen · Accepted solution]

Ah yes, sorry. A bit more complicated, but gets the job done

data have;
input week item store price;
datalines;
1166 23 100 1.50 
1166 23 200 2.50 
1166 23 250 2.50 
1166 25 110 3.25 
1166 25 120 4.50 
1167 23 100 1.50 
1167 23 200 2.50 
1167 23 250 2.50 
1167 25 110 3.25 
1167 25 120 4.50 
;

data want(drop=i j k);
    format week item store store1 price;
    do _n_=1 by 1 until (last.item);
        set have;
        by week item;
        _iorc_+1;
    end;

    i=_iorc_-(_n_-1);

    do j=1 to _n_;
        set have(rename=store=store1);
        do k=i to _iorc_;
            set have point=k;
            output;
        end;    
    end;
run; 

[PaigeMiller]

Having spent more time examining the problem and the explanation from @DavidLie, I was thinking the solution is something called a Cartesian Join, but since both @PeterClemmensen and @Ksharp have provided two different ways to do a Cartesian Join, I won't bother adding mine.

I point out that in PROC SQL, you get a Cartesian Join by using a comma between data sets (instead of some JOIN operator) and by not using the ON clause.

[novinosrin]

Hello @DavidLie My share of fun with your STORE1 I think

data have;
input WEEK	ITEM	STORE	PRICE;
cards;
1166	23	100	1.5
1166	23	200	2.5
1166	23	250	2.5
1166	25	110	3.25
1166	25	120	4.5
;

data want;
 if _n_=1 then do;
   dcl hash H (ordered: "A") ;
   h.definekey  ("_n_") ;
   h.definedata ("WEEK","ITEM","STORE","PRICE") ;
   h.definedone () ;
   dcl hiter hi('h');
end;
do _n_=1 by 1 until(last.item);
 set have;
 by WEEK	ITEM;
 array t(999999)_temporary_;
 if _n_=1 then store1=store;
 else t(_n_)=store;
 output;
 _rc=h.add();
end;
do _n_=2 to _n_;
 do while(hi.next()=0);
 store1=t(_n_);
  output;
 end;
end;
h.clear();
drop _:;
run;

[PaigeMiller]

---

@DavidLie wrote:

Hi SAS Community,

I would like to ask how to create multiple copy of observations from a single observation.

At the moment, I have the following:

WEEK	ITEM	STORE	PRICE
1166	23	100	1.50
1166	23	200	2.50

---

So the price from STORE 200 is 2.50 and then when you create the row in the output with WEEK 1166 and ITEM 23 and STORE 200, this price of 2.50 overwrites the 1.50 that was in the original table. Is that correct?

[Ksharp]

data have;
input week item store price;
datalines;
1166 23 100 1.50 
1166 23 200 2.50 
1166 23 250 2.50 
1166 25 110 3.25 
1166 25 120 4.50 
;
proc sql;
create table temp as
select distinct item,store from have;

create table want as
 select a.*,b.store2
  from have as a,temp(rename=(store=store2)) as b
   where a.item=b.item
    order by a.item,store2,store;
quit; 

[DavidLie]

Thanks all for helping me 🙂 Greatly appreciated.

[PeterClemmensen]

Anytime, glad to help 🙂