Quick question about Select if within Do Repeat

[devoidx]

So I am trying to write a simple Do repeat like this:

Do repeat ab= Dx1 to Dx3.
SELECT IF (ab =1234).
end repeat.
EXECUTE.

my intention is to select cases where 1234 is the value for either of the Dx variables (or all 3..as long as its present for one or more Dx values for a given case)..so whether Dx1 is 1234 or Dx3 is 1234 (regardless of the value for other 2 Dx variables), i want that case to be selected. For some reason, it only selects cases where all 3 Dx variables are 1234 in stead of doing an OR...why?

[Bruce Weaver]

Does this do what you want?

SELECT IF ANY(1234, Dx1, Dx2, Dx3).

devoidx wrote

So I am trying to write a simple Do repeat like this:

Do repeat ab= Dx1 to Dx3.
SELECT IF (ab =1234).
end repeat.
EXECUTE.

my intention is to select cases where 1234 is the value for either of the Dx variables (or all 3..as long as its present for one or more Dx values for a given case)..so whether Dx1 is 1234 or Dx3 is 1234 (regardless of the value for other 2 Dx variables), i want that case to be selected. For some reason, it only selects cases where all 3 Dx variables are 1234 in stead of doing an OR...why?

[devoidx]

In theory yes but in practice i have about 36 Dx variables and quite a few different '1234's that I need to fish for so i was thinking of doing a

Do repeat ab= Dx1 to Dx36.
SELECT IF Any(ab,1234,5434,23445,234234....etc).
end repeat.
EXECUTE.

which would shorten my code quite a bit.

[Maguin, Eugene]

How about this.

Do repeat ab= Dx1 to Dx36.
IF Any(ab,1234,5434,23445,234234....etc) pick=1.
end repeat.
EXECUTE.
Select if (pick eq 1).

Gene Maguin

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of devoidx
Sent: Monday, May 19, 2014 2:30 PM
To: [hidden email]
Subject: Re: Quick question about Select if within Do Repeat

In theory yes but in practice i have about 36 Dx variables and quite a few different '1234's that I need to fish for so i was thinking of doing a

Do repeat ab= Dx1 to Dx36.
SELECT IF Any(ab,1234,5434,23445,234234....etc).
end repeat.
EXECUTE.

which would shorten my code quite a bit.



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Quick-question-about-Select-if-within-Do-Repeat-tp5726138p5726140.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[devoidx]

Thanks, yes that's be a solution that I thought of aswell but since I'm running this through a database with 1.8 billion records, I was trying to avoid any extra computations as every little extra thing to do on my database takes foreverrrrr.

I find it strange that when computing the "pick", it does it the way I want it to as in it assigns a value of 1 when either of the Dx variables contains my value of interest but when I do a straight up select if without computing a new variable it only selects cases that ALL  Dx variables contain my value of interest. I don't understand why it doesn't do the select if the same way that it compute the "pick" variable

[David Marso]

Get RID of the EXECUTE and just do your thing.
That is one data pass which is unnecessary.
One thing you could do is use a VECTOR to hold your variables (assuming they are contiguous) and use a conditional END LOOP.
If the variables are NOT contiguous then make them so with:

MATCH FILES /FILE * / KEEP <variable list> ALL.

Then use:

VECTOR v=<variable list>.
LOOP #=1 TO <number of variables>
COMPUTE pick=any(V(#),<comma delimited value list>).
END LOOP IF pick.

devoidx wrote

Thanks, yes that's be a solution that I thought of aswell but since I'm running this through a database with 1.8 billion records, I was trying to avoid any extra computations as every little extra thing to do on my database takes foreverrrrr.

I find it strange that when computing the "pick", it does it the way I want it to as in it assigns a value of 1 when either of the Dx variables contains my value of interest but when I do a straight up select if without computing a new variable it only selects cases that ALL  Dx variables contain my value of interest. I don't understand why it doesn't do the select if the same way that it compute the "pick" variable

[Rich Ulrich]

David has given good code, allowing you to select "Pick"
with good efficiency.

In answer to "Why?"

I never tried using more than one SELECT IF, so I was interested
in reading what SPSS does for that, since AND or OR both seem
arguably consistent, depending on how "flags" are used.

 - You have just run a test and have learned :  Each one potentially
marks a row as "don't use" and so the cumulative effect is the same
as having AND on the IF conditions.   That is probably a lot easier to
justify than having OR .... I can imagine being very annoyed if my
SELECT IF near the end of a set of syntax incidentally ignored my
SELECT IF near the beginning that I had long ago taken for granted.

--
Rich Ulrich

> Date: Mon, 19 May 2014 11:16:42 -0700

> From: [hidden email]
> Subject: Quick question about Select if within Do Repeat
> To: [hidden email]
>
> So I am trying to write a simple Do repeat like this:
>
> Do repeat ab= Dx1 to Dx3.
> SELECT IF (ab =1234).
> end repeat.
> EXECUTE.
>
> my intention is to select cases where 1234 is the value for either of the Dx
> variables (or all 3..as long as its present for one or more Dx values for a
> given case)..so whether Dx1 is 1234 or Dx3 is 1234 (regardless of the value
> for other 2 Dx variables), i want that case to be selected. For some reason,
> it only selects cases where all 3 Dx variables are 1234 in stead of doing an
> OR...why?
>
>
>
>
> --
> View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Quick-question-about-Select-if-within-Do-Repeat-tp5726138.html
> Sent from the SPSSX Discussion mailing list archive at Nabble.com.
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email] (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD

[Javier Figueroa]

Hola!!

Prueba esto, talvez te sirva. 

DATASET ACTIVATE Conjunto_de_datos1.

/*****VERIFICANDO VARIABLE "A" SIMPLE CONTENGA CODIGOS INDICADOS EN VARIABLE ab*****.

VECTOR ab=Dx1 TO Dx36.       /* SET DE VARIABLES A EXPLORAR.

-           DO IF NOT MISSING(ab).

-                    COMPUTE F2=0.

-                    LOOP #K=1 TO 36.

-                              IF Any(ab(#K), 1234,5434,23445,234234....etc.) F2=1.

-                    END LOOP.

-                    DO IF F2=1.

-                        PRINT / 'Datos Códigos MUL ---> '  ab(#K).

-                    END IF.

-           END IF.

EXECUTE.

Atentamente, 

2014-05-19 14:57 GMT-06:00 Rich Ulrich <[hidden email]>:

David has given good code, allowing you to select "Pick"
with good efficiency.

In answer to "Why?"

I never tried using more than one SELECT IF, so I was interested
in reading what SPSS does for that, since AND or OR both seem
arguably consistent, depending on how "flags" are used.

 - You have just run a test and have learned :  Each one potentially
marks a row as "don't use" and so the cumulative effect is the same
as having AND on the IF conditions.   That is probably a lot easier to
justify than having OR .... I can imagine being very annoyed if my
SELECT IF near the end of a set of syntax incidentally ignored my
SELECT IF near the beginning that I had long ago taken for granted.

--
Rich Ulrich

> Date: Mon, 19 May 2014 11:16:42 -0700
> From: [hidden email]
> Subject: Quick question about Select if within Do Repeat
> To: [hidden email]

>
> So I am trying to write a simple Do repeat like this:
>
> Do repeat ab= Dx1 to Dx3.
> SELECT IF (ab =1234).
> end repeat.
> EXECUTE.
>
> my intention is to select cases where 1234 is the value for either of the Dx
> variables (or all 3..as long as its present for one or more Dx values for a
> given case)..so whether Dx1 is 1234 or Dx3 is 1234 (regardless of the value
> for other 2 Dx variables), i want that case to be selected. For some reason,
> it only selects cases where all 3 Dx variables are 1234 in stead of doing an
> OR...why?
>
>
>
>
> --
> View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Quick-question-about-Select-if-within-Do-Repeat-tp5726138.html
> Sent from the SPSSX Discussion mailing list archive at Nabble.com.
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email] (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD

--
Javier Figueroa
Procesamiento y Análisis de bases de datos

[David Marso]

Upon reflection, I find it strange that someone would find this strange!
Clearly a failure to grok the implications of SELECT IF.
You observed that SELECT IS conjunctive rather than disjunctive in the logical sense!
Well, this makes absolute sense and I would be distressed if it did otherwise!
First pass (and subsequent) of the DO REPEAT a case is either selected or NOT.
Those that are NOT are not even considered in subsequent passes of the DO REPEAT.
Why would you ever expect it to behave like an OR statement?

devoidx wrote

Thanks, yes that's be a solution that I thought of aswell but since I'm running this through a database with 1.8 billion records, I was trying to avoid any extra computations as every little extra thing to do on my database takes foreverrrrr.

I find it strange that when computing the "pick", it does it the way I want it to as in it assigns a value of 1 when either of the Dx variables contains my value of interest but when I do a straight up select if without computing a new variable it only selects cases that ALL  Dx variables contain my value of interest. I don't understand why it doesn't do the select if the same way that it compute the "pick" variable

[David Marso]

Code go boom boom?
No Hablo Espanol!

-           DO IF NOT MISSING(ab). ??????? THIS LINE WILL NOT COMPILE Boom Boom!!????
-                    LOOP #K=1 TO 36.
-                      COMPUTE F2=Any(ab(#K),1234,5434,23445,234234....etc.).
-              END LOOP IF F2.
........

Javier Figueroa wrote

Hola!!

Prueba esto, talvez te sirva.

DATASET ACTIVATE Conjunto_de_datos1.
/*****VERIFICANDO VARIABLE "A" SIMPLE CONTENGA CODIGOS INDICADOS EN
VARIABLE ab*****.
VECTOR ab=Dx1 TO Dx36.       /* SET DE VARIABLES A EXPLORAR.
-           DO IF NOT MISSING(ab).
-                    COMPUTE F2=0.
-                    LOOP #K=1 TO 36.
-                              IF Any(ab(#K),
1234,5434,23445,234234....etc.) F2=1.
-                    END LOOP.
-                    DO IF F2=1.
-                        PRINT / 'Datos Códigos MUL ---> '  ab(#K).
-                    END IF.
-           END IF.
EXECUTE.

Atentamente,



2014-05-19 14:57 GMT-06:00 Rich Ulrich <[hidden email]>:

> David has given good code, allowing you to select "Pick"
> with good efficiency.
>
> In answer to "Why?"
>
> I never tried using more than one SELECT IF, so I was interested
> in reading what SPSS does for that, since AND or OR both seem
> arguably consistent, depending on how "flags" are used.
>
>  - You have just run a test and have learned :  Each one potentially
> marks a row as "don't use" and so the cumulative effect is the same
> as having AND on the IF conditions.   That is probably a lot easier to
> justify than having OR .... I can imagine being very annoyed if my
> SELECT IF near the end of a set of syntax incidentally ignored my
> SELECT IF near the beginning that I had long ago taken for granted.
>
> --
> Rich Ulrich
>
> > Date: Mon, 19 May 2014 11:16:42 -0700
> > From: [hidden email]
> > Subject: Quick question about Select if within Do Repeat
> > To: [hidden email]
>
> >
> > So I am trying to write a simple Do repeat like this:
> >
> > Do repeat ab= Dx1 to Dx3.
> > SELECT IF (ab =1234).
> > end repeat.
> > EXECUTE.
> >
> > my intention is to select cases where 1234 is the value for either of
> the Dx
> > variables (or all 3..as long as its present for one or more Dx values
> for a
> > given case)..so whether Dx1 is 1234 or Dx3 is 1234 (regardless of the
> value
> > for other 2 Dx variables), i want that case to be selected. For some
> reason,
> > it only selects cases where all 3 Dx variables are 1234 in stead of
> doing an
> > OR...why?
> >
> >
> >
> >
> > --
> > View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/Quick-question-about-Select-if-within-Do-Repeat-tp5726138.html
> > Sent from the SPSSX Discussion mailing list archive at Nabble.com.
> >
> > =====================
> > To manage your subscription to SPSSX-L, send a message to
> > [hidden email] (not to SPSSX-L), with no body text except the
> > command. To leave the list, send the command
> > SIGNOFF SPSSX-L
> > For a list of commands to manage subscriptions, send the command
> > INFO REFCARD
>



--



*Javier FigueroaProcesamiento y Análisis de bases de datos*

[devoidx]

David Marso wrote

Upon reflection, I find it strange that someone would find this strange!
Clearly a failure to grok the implications of SELECT IF.
You observed that SELECT IS conjunctive rather than disjunctive in the logical sense!
Well, this makes absolute sense and I would be distressed if it did otherwise!
First pass (and subsequent) of the DO REPEAT a case is either selected or NOT.
Those that are NOT are not even considered in subsequent passes of the DO REPEAT.
Why would you ever expect it to behave like an OR statement?

Thanks for all the replies. Reason I expected it to behave like an OR statement is because the Do repeat does seem to create an OR environment. forexample:

Do repeat ab= test1 to test50.
if (ab=1) pick=666.
end repeat.

in this scenario, "pick" as assigned a variable of 666 if test1 = 1 OR test2=1 OR test3=1 etc...
so I was expecting that putting a select if in this environment would select for the same conditions...as in Select if test1=1 OR test=1 etc...

[David Marso]

Consider going with the END LOOP IF structure as it will exit the loop on the first occurrence of locating a target value.  The DO REPEAT will traverse the entire list.  With 1+ Billion cases this might make a big difference.  I'm not sure if ANY will drop out immediately.

[David Marso]

You might also look at the COUNT command followed by SELECT IF.
Might be more efficient than what we have so far proposed.

COUNT creates a numeric variable that, for each case, counts the occurrences of the same value (or list of values) across a list of variables. The new variable is called the target variable. The variables and values that are counted are the criterion variables and values. Criterion variables can be either numeric or string.

Basic Specification

The basic specification is the target variable, an equals sign, the criterion variable(s), and the criterion value(s) enclosed in parentheses.

eg: COUNT newvar=<Varlist> (value list).

[devoidx]

Thanks! ill play around with your suggestions tomorrow

[devoidx]

couldn't sleep so i tried the count idea..it definitely does work when followed by select if as you mentioned. Problem is it again needs to compute a count variable for all my 1.8 billion cases. It looks like no matter which method i go with I'm gonna have to end up with creating new variables to run the select if off of. At least I learned a few new things from these discussions!

David Marso wrote

You might also look at the COUNT command followed by SELECT IF.
Might be more efficient than what we have so far proposed.

COUNT creates a numeric variable that, for each case, counts the occurrences of the same value (or list of values) across a list of variables. The new variable is called the target variable. The variables and values that are counted are the criterion variables and values. Criterion variables can be either numeric or string.

Basic Specification

The basic specification is the target variable, an equals sign, the criterion variable(s), and the criterion value(s) enclosed in parentheses.

eg: COUNT newvar=<Varlist> (value list).

[David Marso]

Why not use a scratch variable as result of whatever method you choose?
That way you don't need to delete it later.

devoidx wrote

couldn't sleep so i tried the count idea..it definitely does work when followed by select if as you mentioned. Problem is it again needs to compute a count variable for all my 1.8 billion cases. It looks like no matter which method i go with I'm gonna have to end up with creating new variables to run the select if off of. At least I learned a few new things from these discussions!

David Marso wrote

You might also look at the COUNT command followed by SELECT IF.
Might be more efficient than what we have so far proposed.

COUNT creates a numeric variable that, for each case, counts the occurrences of the same value (or list of values) across a list of variables. The new variable is called the target variable. The variables and values that are counted are the criterion variables and values. Criterion variables can be either numeric or string.

Basic Specification

The basic specification is the target variable, an equals sign, the criterion variable(s), and the criterion value(s) enclosed in parentheses.

eg: COUNT newvar=<Varlist> (value list).

[devoidx]

I didn't even know about scratch variables..read up on it...it's a cool concept, though won't save me from having to compute a new variable for my gignantic database but as you said it would avoid the hassle of getting rid of the newly computed variables..

[David Marso]

I'm afraid I don't grasp why this is an issue! SO you have to create a variable. LIVE WITH IT!
Much better than
SELECT IF ANY(ANY(var1,x,xxx,xxxx,xxxxx),ANY(var2,xxx,xxxx,xxxxx),...............ANY(varK,.........) ).
The latter is FUGLY, virtually unmaintainable and unlikely to be any faster than our previous suggestions!!!!!!!

devoidx wrote

I didn't even know about scratch variables..read up on it...it's a cool concept, though won't save me from having to compute a new variable for my gignantic database but as you said it would avoid the hassle of getting rid of the newly computed variables..

[devoidx]

Well looks like I am going to have to live with it ..just wanted to make sure I'm not missing any obvious strategies that do not require creating new variables.

If you were dealing with a multi-billion case database on an average computer, you would perhaps understand better why I am trying find a code with with the least number of computations etc in it.

To be honest I still don't understand why:

Do repeat ab= test1 to test10.
if (ab=1) pick=1.
end repeat.

acts like an OR when assigning the "pick" values while:

Do repeat ab= test1 to test10.
select if (ab=1).
end repeat.

acts like an AND.

But i think we're beating a dead horse here so thanks for all the help!

[David Marso]

Well absolutely NOTHING obvious about this but it is more easy to maintain than a LONG explicit SELECT within a bunch of code (especially if it is going to change in any way from day to day). NOTE! MACROS expand PRIOR to any data reading!
Regarding And/Or issue, several of us have provided pretty clear explanations.
The Horse has already been processed at the glue factory ;-)
SELECT IF (condition) IMMEDIATELY REMOVES FROM FURTHER CONSIDERATION ANY CASES WHICH DO NOT MEET THE CONDITION.  If you have a set of conditions implied by some loop, ONLY CASES WHICH MATCH ALL CONDITIONS REMAIN!
---
DEFINE BigASSFUGLYSELECT (vars !CHAREND("/") /values !CMDEND).
!LET !ValList =","
!LET !cpy=!values
!DO !val !IN (!values)
!LET !cpy=!TAIL(!cpy)
!LET !ValList = !CONCAT(!ValList,!val)
!IF (!cpy !NE !NULL) !THEN
!LET !ValList=!CONCAT(!ValList,",")
!IFEND
!DOEND
!LET !ValList=!CONCAT(!ValList,")" )
!LET !SEL="SELECT IF ANY(1,"
!LET !Cpy=!Vars
!DO !Var !IN (!Vars)
!LET !Cpy=!TAIL(!Cpy)
!LET !SEL=!CONCAT(!SEL,'ANY(',!Var,!ValList)
!IF (!cpy !NE !NULL) !THEN
!LET !SEL=!CONCAT(!SEL,",")
!IFEND
!DOEND
!LET !SEL=!CONCAT(!SEL,")")
!SEL
!ENDDEFINE.
SET MPRINT ON PRINTBACK ON.
BigASSFUGLYSELECT vars x1 x2 x3 x4 x5 / values 1 2 3 4 5 .

Generates the following fugly syntax.
 SELECT IF ANY(1,ANY(x1,1,2,3,4,5),ANY(x2,1,2,3,4,5),ANY(x3,1,2,3,4,5),ANY(x4,1,2,3,4,5),ANY(x5,1,2,3,4,5))

devoidx wrote

Well looks like I am going to have to live with it ..just wanted to make sure I'm not missing any obvious strategies that do not require creating new variables.

If you were dealing with a multi-billion case database on an average computer, you would perhaps understand better why I am trying find a code with with the least number of computations etc in it.

To be honest I still don't understand why:

Do repeat ab= test1 to test10.
if (ab=1) pick=1.
end repeat.

acts like an OR when assigning the "pick" values while:

Do repeat ab= test1 to test10.
select if (ab=1).
end repeat.

acts like an AND.

But i think we're beating a dead horse here so thanks for all the help!