How to create subset of a dataset based on unique values of a variable available in other dataset

[Ronin]

Hi, 

I am a novice to programming world need help.

I have two datasets:

dataset1: (with 620 unique STOREID and with onemore column for var2)

STOREID	Var2
1234	xxxx
567	YYYY
7332	aaaa
6664	ttt
87	cdes
20086	aoia
129743	uytrewl

dataset2: (with 34000 observation and 86 variables including STOREID)

Var1	Var2	Var3	Var4	STOREID	Var5	Var6	Var7	Var8
454	0	a	8	1234	f9s97s	9kj876	83
				1234	087jn	9kj876	83	8323
16.987	1	t	8	1234	098ju5	9kj876	83	832341
0.003	1	y	8	567	4h7s	9kj876	831	3783232
1098	2	i	8	567	9i86	9kj876	983	3832359
987	4	ac	8	6664	9juy	9kj876	83	38323
097	7	cf	8	90	a764	9kj876	1	832325
986	9	fd	8	90	6jh	9kj876	3	3832132

I want to create another sas dataset from dataset2 by selecting all the observations from dataset2 for each of the STOREID present in dataset1. Can anybody help me with the code.(Note: I dont want to merge the given datsets, its more like index-match in excel, taking reference STOREID value from dataset1 and selecting all observations for the same reference STOREID value from dataset2 and creating a new datset with those observations)   

[RW9 · Accepted solution]

"Note: I dont want to merge the given datsets," - then you cannot match the data.  Data has to appear in the same place to be evaluated.  Probably the simplest method is:

proc sql;
  create table want as
  select *
  from   ds2
  where storeid in (select storeid from ds1);
quit;

That is however doing a sort of merged table in the background.

[Ronin]

Hey Hi, thanks for the solution. it worked. But this just provides me the log file which tells tells the info that table got created with 7095 rows and 86 observation..but din't get me a table with values to see. I want the table as a sas dataset(.sas7bdat) file. Can you help me with this.

[Kurt_Bremser]

---

@Ronin wrote:
Hey Hi, thanks for the solution. it worked. But this just provides me the log file which tells tells the info that table got created with 7095 rows and 86 observation..but din't get me a table with values to see. I want the table as a sas dataset(.sas7bdat) file. Can you help me with this.

---

If SAS tells you it created something, then it did, period. It may just be that your Enterprise Guide or SAS Studio is not set to automatically open results.

In case of other unclear issues, post the log (use the {i} button so that the log contents are not mangled by the forum software).

Regarding your request for code: if you provide example data in usable form (data steps with datalines, see my footnotes for creating such automatically and how to post the code), I can fiddle around with it and give you some examples.

[RW9]

The code I provided has a create table want as statement, which creates a dataset called want in the work library.

[Kurt_Bremser]

Probable methods:

1. join in SQL
2. merge in a data step
3. using a hash object in a data step to keep dataset 1 as a reference
4. create a format from dataset 1 and use that

1 & 2 are the most straight-forward ways, recognizable by any programmer who looks at it

3 & 4 need a little more expertise on the part of the programmer (especially 3), but give you better performance (no sorts needed).

OTOH with 34k and 600 observations, this will be done in fractions of a second anyway.

Edit: fixed a typo.

[Ronin]

Thanks for explaining me about all the possible solutions. Can you please help me with any of this code