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

Hello SAS communities,

 

I want to merge a data set of individuals shopping at stores and store information. I have data set of individuals shop at which store and another dataset with store information. The two data sets are not of same size. How can I merge the information of store into the individual shopping data? 

 

Example of my data:

Individual data like this:

id   store

1      1

1      2

2      1

3      3

4      2

....

 

Store data like this: 

store  location   convenience    inventory 

1           1                   7                 5

2           4                   8                 6

3           3                   8                 5

....

 

I want a data like this:

id   store  location   convenience    inventory

1      1      1                 7                    5

1      2      4                 8                    6 

2      1      1                 7                    5 

3      3      3                 8                    5

4      2      4                 8                    6 

....

 

Thanks!

                 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

Hi @mkt_apprentice 

 

A tested one-->

 

data indiv;
input id   store ;
cards;
1      1
1      2
2      1
3      3
4      2
;

data store;
input store  location   convenience    inventory  ;
cards;
1           1                   7                 5
2           4                   8                 6
3           3                   8                 5
;

proc sql;
create table want as
select a.*,location ,  convenience ,   inventory
from indiv a inner join store b
on a.store=b.store
order by a.id, store;
quit;

View solution in original post

3 REPLIES 3
novinosrin
Tourmaline | Level 20

untested

 

data want;

merge indiv store;

by  store;

run;

 

sort both the datasets by store before the merge

novinosrin
Tourmaline | Level 20

Hi @mkt_apprentice 

 

A tested one-->

 

data indiv;
input id   store ;
cards;
1      1
1      2
2      1
3      3
4      2
;

data store;
input store  location   convenience    inventory  ;
cards;
1           1                   7                 5
2           4                   8                 6
3           3                   8                 5
;

proc sql;
create table want as
select a.*,location ,  convenience ,   inventory
from indiv a inner join store b
on a.store=b.store
order by a.id, store;
quit;
mkt_apprentice
Obsidian | Level 7

Again, thank you @novinosrin !

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1149 views
  • 1 like
  • 2 in conversation