How to calculate (sum) from multiple variable thru loop, if variable are not in sequential order

[Satish]

Respected member,

I have a query please help me on this as soon as possible.

I have set of 314 variable starting from Q6I_T_1 to Q6I_T_246 and Q6I_T_1001 to Q6I_T_1068. But all these variables are not in order because we have other set of variable in between. I have tried below mentioned syntax and it's working fine if I am putting range around 100. when i am putting range till 186 or more it's not calculating anything.

note : I have few varibles where data is missing.

Can you help me how i can get total sum of all variables starting from Q6I_T_1 to Q6I_T_1068 . it will great help.

COMPUTE total_share1 = 0.
DEFINE !TSum ().
!DO !t = 1 !TO 100.
!LET !XSum = !CONCAT("Q6I_T_",!t).
!LET !invars = !CONCAT("sum(",!XSum,")").
COMPUTE total_share1 = total_share1+!invars.
!DOEND.
!ENDDEFINE.
!TSum.
EXECUTE.

thanks in advance.

RegardsTest.sav
SAT

[Bruce Weaver]

Hello Satish.  Using the data you attached (thanks for that), the following code generated no errors or warnings.

COMPUTE total_share1 = 0.
DEFINE !TSum ().
!DO !t = 1 !TO 186.
!LET !v = !CONCAT("Q6I_T_",!t).
IF NOT MISSING(!v) total_share1 = total_share1+!v.
!DOEND.
!ENDDEFINE.
SET MPRINT ON.
!TSum.
SET MPRINT OFF.
DESCRIPTIVES total_share1.


Notice that I used IF where you used COMPUTE to ensure that I did not try to add any missing variables to the running sum.  I also did not understand your !invars variable with SUM().  Why is that needed?  

If this revised code gives the result you want, note that you can comment out or remove the two SET MPRINT lines once you know everything is working correctly.  

HTH.

Satish wrote

Respected member,

I have a query please help me on this as soon as possible.

I have set of 314 variable starting from Q6I_T_1 to Q6I_T_246 and Q6I_T_1001 to Q6I_T_1068. But all these variables are not in order because we have other set of variable in between. I have tried below mentioned syntax and it's working fine if I am putting range around 100. when i am putting range till 186 or more it's not calculating anything.

note : I have few varibles where data is missing.

Can you help me how i can get total sum of all variables starting from Q6I_T_1 to Q6I_T_1068 . it will great help.

COMPUTE total_share1 = 0.
DEFINE !TSum ().
!DO !t = 1 !TO 100.
!LET !XSum = !CONCAT("Q6I_T_",!t).
!LET !invars = !CONCAT("sum(",!XSum,")").
COMPUTE total_share1 = total_share1+!invars.
!DOEND.
!ENDDEFINE.
!TSum.
EXECUTE.

thanks in advance.

RegardsTest.sav
SAT

[Satish]

Hello Bruce,

I really appreciate your quick response and support. Thank you so much.

Yes, it's working thanks for that.


Regards
   

[Art Kendall]

*Your data does not have defined user missing values.

*You do not have labels so it is hard to know if the question posed is really what you want to know.  It is very possible a sum is not meaningful.

*Sysmis values have not been resolved.

one work around is this

SORT VARIABLES BY NAME (A).
compute MySum =SUM( Q6I_T_1 to Q6I_T_246, Q6I_T_1001 to Q6I_T_1068).
FREQUENCIES VARIABLES = MySum.

FREQUENCIES VARIABLES=Q6I_T_1016
  /STATISTICS= sum MINIMUM MAXIMUM STDDEV MEAN MEDIAN.

[Satish]

Hi Bruce,

Sorry to bother you again.

Is there any way to create multiple variable thru any macro/loop. I have two calculation below where I need to generate separate variable for each code. I need to create 200 variables for each calculation starting from "_1" to "_200".

1.

COMPUTE SAT_NUM_DIS_1=0.
IF (SAT_NUM_AVA_1=1 OR (Q8A_1=1 AND Q8B_1=1))SAT_NUM_DIS_1=1.
EXECUTE.

2.

COMPUTE SAT_NUM_OOS_1=$SYSMIS.
IF(SAT_NUM_DIS_1=1)SAT_NUM_OOS_1=0.
EXECUTE.


Regards

[PRogman]

I am looking over your data set. You have 556 cases and 7735 variables.
The variable structure seems to have 5 initial unique names, then a block of variables starting with Q5_10_# thru Q67_T_#, where # changes from 2 to 246 and then from 1000 to 1068.
Maybe you are working in the wrong (wide) format. Some tasks are more difficult in the wrong database format. Do you have data in long format? If so, FILTER and AGGREGATE may solve your problems.
BTW, the variables SAT_NUM_AVA_# are not inte file.
 
Apparnet variable structure in Test.sav:
Initial 5 variables
  SRL        (not unique values, 162 duplicates)
  Q6I_T_1  
  Q6II_T_1
  Q7_T_1  
  Q67_T_1
then repeat # = [2...246, 1001...1068]
    Q5_10_#    Q5_20_#
    Q6I_10_#   Q6I_20_#
    Q6II_10_#  Q6II_20_#
    Q7_10_#    Q7_20_#
    Q8A_#       Q8B_#
    Q9MN_1_#  Q9YR_1_#
    Q9MN_2_#  Q9YR_2_#
    Q9MN_3_#  Q9YR_3_#
    NUM_DIS_#
    NUM_OOS_#
    EXPIR_#
    WT_DIS_#
    WT_OOS_#
    Q5_T_#    
    Q6I_T_#   Q6II_T_#
    Q7_T_#    Q67_T_#

/PR

[Bruce Weaver]

Good comments, PR.  You are absolutely right in noting that some tasks are much easier with a LONG file than a WIDE file.  

PRogman wrote

I am looking over your data set. You have 556 cases and 7735 variables.
The variable structure seems to have 5 initial unique names, then a block of variables starting with Q5_10_# thru Q67_T_#, where # changes from 2 to 246 and then from 1000 to 1068.
Maybe you are working in the wrong (wide) format. Some tasks are more difficult in the wrong database format. Do you have data in long format? If so, FILTER and AGGREGATE may solve your problems.
BTW, the variables SAT_NUM_AVA_# are not inte file.
 
Apparnet variable structure in Test.sav:
Initial 5 variables
  SRL        (not unique values, 162 duplicates)
  Q6I_T_1  
  Q6II_T_1
  Q7_T_1  
  Q67_T_1
then repeat # = [2...246, 1001...1068]
    Q5_10_#    Q5_20_#
    Q6I_10_#   Q6I_20_#
    Q6II_10_#  Q6II_20_#
    Q7_10_#    Q7_20_#
    Q8A_#       Q8B_#
    Q9MN_1_#  Q9YR_1_#
    Q9MN_2_#  Q9YR_2_#
    Q9MN_3_#  Q9YR_3_#
    NUM_DIS_#
    NUM_OOS_#
    EXPIR_#
    WT_DIS_#
    WT_OOS_#
    Q5_T_#    
    Q6I_T_#   Q6II_T_#
    Q7_T_#    Q67_T_#

/PR

[Bruce Weaver]

If believe this might give the results you want for the two scenarios described below.  The file you uploaded previously did not include all of these variables, so it is untested.  


DEFINE !SAT_NUM ().
!DO !t = 1 !TO 200.
!LET !DIS = !CONCAT("SAT_NUM_DIS_",!t).
!LET !AVA = !CONCAT("SAT_NUM_AVA_",!t).
!LET !Q8A = !CONCAT("Q8A_",!t).
!LET !Q8B = !CONCAT("Q8B_",!t).
!LET !OOS = !CONCAT("SAT_NUM_OOS_",!t).
COMPUTE !DIS = (!AVA EQ 1 OR (!Q8A EQ 1 AND !Q8B EQ 1)).
RECODE !DIS (1=0) INTO !OOS.
!DOEND
EXECUTE.
!ENDDEFINE.
SET MPRINT ON.
!SAT_NUM.
SET MPRINT OFF.

Satish wrote

Hi Bruce,

Sorry to bother you again.

Is there any way to create multiple variable thru any macro/loop. I have two calculation below where I need to generate separate variable for each code. I need to create 200 variables for each calculation starting from "_1" to "_200".

1.

COMPUTE SAT_NUM_DIS_1=0.
IF (SAT_NUM_AVA_1=1 OR (Q8A_1=1 AND Q8B_1=1))SAT_NUM_DIS_1=1.
EXECUTE.

2.

COMPUTE SAT_NUM_OOS_1=$SYSMIS.
IF(SAT_NUM_DIS_1=1)SAT_NUM_OOS_1=0.
EXECUTE.


Regards

[Art Kendall]

Wide vs long arrangement is one of the possibilities I was thinking about when I asked for more definition of what the underlying question context was.

Why is the sum across those variables meaningful?  The appeared to be many zeros and many sysmis values in test.sav.

But before deciding whether a long or wide data structure is easier to do a task, it is critical to ask the meaningfulness of the task.

[jkpeck]

This is a good case for the SPSSINC SELECT VARIABLES extension command, which you can install via the Extensions > Extension Hub menu.  It creates a macro for the variable names matching a pattern or listed explicitly or by variable type or combinations of these.

In this case, assuming that this pattern selects only the variables you need, you could do this.
SPSSINC SELECT VARIABLES MACRONAME="!T"
/PROPERTIES PATTERN = "Q61_T_"
/OPTIONS  SEPARATOR=", ".

All variable names starting with Q61_T_ would be listed.  I specified the separator as comma.  Then you could just do
compute sum = sum(!T).

If this selects too many variables, you could use the dialog box under Utilities > Define variable macro to restrict the selection further.