datastep and procstep - small question

[Ivan555]

Hello!

While doing my project I do see two ways of dealing with my SAS objects.

Seems 2nd way is preferred(seems it works faster) but can you say, am I correct with it or not?

proc sql;
create table t1 (id int, typ char(3), qty int);
insert into t1 
	values (1, 'aas', 50)
	values (2, 'aaf', 15)
	values (3, 'aaa', 50)
	values (4, 'abd', 5)
	values (5, 'asa', 25)
	values (6, 'afa', 5)
	values (7, 'aba', 15);
quit;

data d1;
input qty2;
datalines;
5
15
;
run;

first option:

proc sql; create table want as select * from t1 where qty in (select qty2 from d1); quit;

second option:

proc sql noprint; select qty2 into: qty3 separated by "," from d1; quit;
proc sql; create table want as select * from t1 where qty in (&qty3); quit;

THX!

[Reeza · Accepted solution]

Yes, in general, the second method is faster, but it doesn't always scale well if the WHERE condition gets more complicated or if the macro variable gets too long you'll need to find a work around. A macro variable can hold 65K characters but you can also generate those values via a macro. I believe a hash is ultimately the fastest for filters if you're looking for efficiency.

[Ivan555]

@Reeza 

Understood, thank you.

Can't you say, if I am doing same with char, where is the mistake?

I tryed, but I am getting an error: 

63 aas, aaf

ERROR 79-332: Expecting a SELECT

ERROR 79-332: Syntax error, statement will be ignored.

data d2;
input typ2 $;
datalines;
aas
aaf
;
run;

proc sql noprint; select typ2 into: typ3 separated by "," from d2; quit;
proc sql; create table want as select * from t1 where typ in (&typ3); quit;

[Reeza]

You didn't generate valid SAS code there.

When you select characters they need to be in quotes and you created a macro variable without quotes.

select * from class where name in (Alfred, Jane, Jim) is what you're using and that isn't valid SAS code.

With quotes, this is correct:

select * from class where name in ("Alfred", "Jane", "Jim")

[Ivan555]

Sorry, you mean because of presence the data in datastep without quotes it cannot being used in macro variables, right?

Or it is possible to somehow add quotes to each element in macro variable?

[Reeza]

Second option, you can add quotes to each element, there's a function, QUOTE().

[Reeza]

when you're writing macros make sure to use the proper debugging options so that your log has enough information for you to see what's going on:

options mprint symbolgen;

MLOGIC is another option that can be useful when things aren't working well, but I also find it can really clutter up your log.

[Ivan555]

It works!

Thank you much, Reeza!

[Ivan555]

@Reeza 

when you're writing macros make sure to use the proper debugging options so that your log has enough information for you to see what's going on:

options mprint symbolgen;

MLOGIC is another option that can be useful when things aren't working well, but I also find it can really clutter up your log.

Thank you, I will study these options.