How SAS run with multiuple arrays in one datastep?

[Phil_NZ]

Hi all, 

Today I want to exclude the stock in specific countries with ENAME contains some characters, my code is as below:

data filter_each_country;
 set concatenate_;
  array delwords1 {18} $ 15 _temporary_ ('PN' 'PNA' 'PNB' 'PNC' 'PND' 'PNE' 'PNF'
'PNG' 'RCSA' 'RCTB' 'PNDEAD' 'PNADEAD' 'PNBDEAD' 'PNCDEAD' 'PNDDEAD' 'PNEDEAD' 'PNFDEAD' 'PNGDEAD');
   do i= 1 to dim(delwords1);
    if GEOGN ='BRAZIL' and findw(ENAME,delwords1[i],'','eir') >0 then delete;
   end;

  array delwords2 {3} $ 15 _temporary_ ('PFCL' 'PRIVILEGIADAS' 'PRVLG');
   do i= 1 to dim(delwords2);
    if GEOGN ='COLOMBIA' and findw(ENAME,delwords2[i],'','eir') >0 then delete;
   end;
 run;

The code is built based on my understanding: the code above runs through the first whole loop and delete the observation satisfying both conditions: having GEOGN='BRAZIL' and ENAME contains the words in array delwords1. Afterwards, SAS will start from observation 1 and run through the whole second loop and delete the observation satisfying both conditions: having GEOGN='COLOMBIA' and ENAME contains the words in array delwords2.

I am wondering if I fall into any fallacy.

Thank you and warm regards.

[ChrisNZ · Accepted solution]

Not exactly.

SAS will read observation 1 and assess whether it should be deleted, using all the tests you have programmed. Then it goes to obs 2 and does the same. It never goes back to obs 1.

[Phil_NZ]

Hi @ChrisNZ 

So, you mean that the first observation will go through all the loops until being deleted, if not it will be read implicitly (if there is no explicit output) at the end, and then the second observation will follow the same pattern.

Warm regards.

[ChrisNZ]

> So, you mean that the first observation will go through all the loops until being deleted, if not it will be read implicitly (if there is no explicit output) at the end, and then the second observation will follow the same pattern.

Rather: the first observation will go through all the loops until being deleted, if not it will be written out implicitly (if there is no explicit output) at the end, and then the second observation will follow the same pattern.

[ChrisNZ]

Note that you can improve speed a bit by using modifiers eit instead or eir.

[japelin]

I don't know what you are wondering about, is there a problem?
Also, I don't know what the value of ENAME is, but it looks like it could be rewritten like this, what do you think?

data filter_each_country;
  set concatenate_;
  array delwords1 {18} $ 15 _temporary_ ('PN' 'PNA' 'PNB' 'PNC' 'PND' 'PNE' 'PNF' 'PNG' 'RCSA' 'RCTB' 'PNDEAD' 'PNADEAD' 'PNBDEAD' 'PNCDEAD' 'PNDDEAD' 'PNEDEAD' 'PNFDEAD' 'PNGDEAD');
  array delwords2 {3} $ 15 _temporary_ ('PFCL' 'PRIVILEGIADAS' 'PRVLG');
  if (GEOGN ='BRAZIL'   and upcase(ENAME) in delwords1) or
     (GEOGN ='COLOMBIA' and upcase(ENAME) in delwords2) then delete;
 run;

This code does not use a do loop on the array, but uses in operator.
Since in operator detects word matches, it is not exactly the same as the findw function,

but since the array was set with duplicate characters such as "PN", "PNA", etc., I decided that it would be possible.
I hope this helps.

[ChrisNZ]

That's not the same thing at all.  findw() looks for a partial match, while your second test using upcase() looks for a full match of the string.

I was saying that modifier eit trims all strings in the expression, thus making the search for a match faster then simply using eir. 

[Phil_NZ]

Hi @ChrisNZ 

Thank you very much for your introduction about "eit", I did a search from google and lexjansen but no document available for me to read about that, could you please suggest me a document or else?

Best regards.

[ChrisNZ]

> Thank you very much for your introduction about "eit", 

Look at the t modifier for function findw()

[Phil_NZ]

Hi @japelin 

Thank you for your reply, the ENAME is as below (I show the deleted items)

TYPE	ENAME
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
772941	STOCKMANN AF DEAD 04/01/93 USE 946148
50879M	GMA HOLDINGS PDR
50879M	GMA HOLDINGS PDR
50879M	GMA HOLDINGS PDR
50879M	GMA HOLDINGS PDR
50879M	GMA HOLDINGS PDR
50879M	GMA HOLDINGS PDR
50879M	GMA HOLDINGS PDR
50879M	GMA HOLDINGS PDR
50879M	GMA HOLDINGS PDR
50879M	GMA HOLDINGS PDR

based on the filtered code:

data concatenate_ ;
	set work.concatenatex;
	where indc3 not in ('NA' 'UNCLS' 'UQEQS' 'OTHEQ');

	if (GEOGN ='PHILIPPINES' and findw(ENAME,'PDR','','eir') >0)
		or
		(GEOGN ='FINLAND' and findw(ENAME,'USE','','eir') >0)
		or
		(GEOGN ='NEWZEALAND' and findw(ENAME,'RTS','','eir') >0) then
		DELETE;
run;

Warm regards,

[japelin]

@Phil_NZ 

It is hard to say without checking what the value to be deleted looks like.

If it "contains a word" specified in the array, then you need findw, and if it "matches a character" specified in the array, then you can use in operator.

Let's take the case of "GEOGN ='COLOMBIA'" as an example.
If the value of ENAME is "PFCL XXX", findw will return >0, but in operator will return false.
If the value of ENAME is "PFCL", findw will return >0, and in operator will return true, so it can be rewritten.

Also, please don't multi-post.