topic Re: How to create subset of a dataset based on unique values of a variable available in other datase in SAS Programming

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

Ronin — Thu, 15 Nov 2018 12:49:10 GMT

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

1234

f9s97s

9kj876

1234

087jn

9kj876

8323

16.987

1234

098ju5

9kj876

832341

0.003

567

4h7s

9kj876

831

3783232

1098

567

9i86

9kj876

983

3832359

987

6664

9juy

9kj876

38323

097

a764

9kj876

832325

986

6jh

9kj876

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)

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

RW9 — Thu, 15 Nov 2018 13:03:18 GMT

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

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

Kurt_Bremser — Fri, 16 Nov 2018 08:21:47 GMT

Probable methods:

join in SQL
merge in a data step
using a hash object in a data step to keep dataset 1 as a reference
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.

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

Ronin — Fri, 16 Nov 2018 06:17:45 GMT

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

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

Ronin — Fri, 16 Nov 2018 06:28:48 GMT

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.

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

Kurt_Bremser — Fri, 16 Nov 2018 06:58:38 GMT

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

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

RW9 — Fri, 16 Nov 2018 08:03:48 GMT

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