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
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;
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...
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
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;
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
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;
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.
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;
@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?
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;
Anytime, glad to help 🙂
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.