BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

Hello

I have a data set with list of customer accounts and ID.

(Each customer can be owned by multiple ID's and specific ID can be also in multiple accounts).

I want to select rows from "have data set"  of all ID's  of customer  1234567 .

Way1 of solution i working well.

I want to ask why way2 is not working well and what is the way to fix it?

Are there other ways to solve it to get the wanted data set?

 

thanks

 

 

 

 


Data have;
input IP customerID;
cards;
111 1234567
222 1234567
333 8788888
444 8798777
555 8765432
222 8765432
222 7654221
111 8273774
;
Run;

PROC SQL;
	create table tbl1  as
	select distinct IP  	   
	from  have
	where customerID=1234567
;
QUIT;

PROC SQL;
	create table wanted_way1  as
	select a.* 	   
	from  have as a
	inner join tbl1 as b
	on a.IP=b.IP
;
QUIT;

Data wanted_way2;
set have(where=(IP IN (select  distinct IP from  have 	where customerID=1234567)));
Run;
3 REPLIES 3
jimbarbour
Meteorite | Level 14

Well, your second method is a data step.  Inside the data step, you have a WHERE with a Select.  As far as I know, Select is valid in Proc SQL but is not valid in a Data step.

 

There are multiple ways you  could get it working including a merge, a hash table, or arrays.

 

Given what you've already got, perhaps a merge is simplest.  Here is an example:

Proc	SORT	DATA=Have;
	BY	IP	CustomerID;
RUN;

Proc	SORT	DATA=Tbl1;
	BY	IP;
RUN;

Data wanted_way2_merge;
	MERGE	Have	(IN	=	Have)
			Tbl1	(IN	=	IP_List);
		BY	IP;
	IF	Have and IP_List;
Run;

 

Jim

jimbarbour
Meteorite | Level 14

And here are examples using an array and a hash table below. Note that for the array I inserted %LET IP_Cnt = &SQLOBS; right after the SQL that creates Tbl1.

 

So, now you have four methods:

  1. SQL
  2. A merge
  3. An array
  4. A hash table

Take your pick.

 

Data wanted_way2_array;
	DROP	_:;
	IF	_N_	=	1	THEN
		DO;
			DO	_i	=	1	TO	&IP_Cnt;
				SET	Tbl1;
				ARRAY	IP_List	{&IP_Cnt}	_TEMPORARY_;
				IP_List{_i}	=	IP;
			END;
		END;
	SET	Have;
	IF	IP	IN	IP_List;
Run;

Data wanted_way2_hash;
	IF	0			THEN
		SET	Tbl1;
	IF	_N_	=	1	THEN
		DO;
			DECLARE	Hash	h_IP_List(DATASET: 	'Tbl1');
							h_IP_List.DEFINEKEY	('IP');
							h_IP_List.DEFINEDONE();
		END;
	SET	Have;
	IF	h_IP_List.FIND()	=	0;
Run;

Jim

 

Kurt_Bremser
Super User

Way 2 is not just "not working well", it doesn't work at all!

Please follow Maxim 2 and read the log!

 

To use a WHERE= in a data step, you need an intermediate step and a macro variable:

proc sql noprint;
select distinct ip into :iplist separated by ","
from have
where customerID=1234567;
quit;

data wanted_way2;
set have (where=(ip in (&iplist.)));
run;

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 543 views
  • 1 like
  • 3 in conversation