putting a caption in tables through a macro

[progster]

Dear All,

I prepared this macro in order to obtain separate tables for each quarter in my data, but I would like to add a caption before exporting the output to Excel.


data list list / quarter m1.
begin data.
2 84
2 85
3 45
4 27
4 57
4 82
end data.




set mprint off.
define mytab (cat=!charend("/") /
capt=!cmdend).
!do !I !IN (!CAT)
!do !J !IN (!capt)
temp.
sel if quarter=!I.
CTABLES
  /VLABELS VARIABLES=m1 DISPLAY=LABEL
  /TABLE m1 [C][COUNT F40.0, COLPCT.COUNT PCT40.0]
  /CATEGORIES VARIABLES=m1 ORDER=D KEY=COUNT EMPTY=include TOTAL=YES POSITION=AFTER
 /TITLES
    TITLE='Company'
CAPTION=!capt.

!DOEND
!DOEND
!ENDDEFINE.

SET MPRINT ON.


mytab CAT= 2 3 4 /capt = 'Q2' 'Q3' 'Q4'.

Now with this syntax in each table I see "Q2 Q3 Q4" instead of the proper quarter.

Is it possible to obtain what I'm looking for?

Thanks

[Manoj Arora (DEL/Abacus Analytics)]

Hi,

 

I have done the changes in the macro and try this. The changes are highlighted in yellow color.

 

Regards

Manoj

 

 

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of progster
Sent: Tuesday, April 07, 2015 4:00 PM
To: [hidden email]
Subject: putting a caption in tables through a macro

 

Dear All,

 

I prepared this macro in order to obtain separate tables for each quarter in my data, but I would like to add a caption before exporting the output to Excel.

 

 

data list list / quarter m1.

begin data.

2 84

2 85

3 45

4 27

4 57

4 82

end data.

 

 

 

 

set mprint off.

define mytab (cat=!charend("/") /

capt=!cmdend).

!do !I !IN (!CAT)

!do !J !IN (!capt)

 

!let !k1=!unquote(!j)

 

temp.

sel if quarter=!I.

CTABLES

  /VLABELS VARIABLES=m1 DISPLAY=LABEL

  /TABLE m1 [C][COUNT F40.0, COLPCT.COUNT PCT40.0]

  /CATEGORIES VARIABLES=m1 ORDER=D KEY=COUNT EMPTY=include TOTAL=YES

POSITION=AFTER

/TITLES

    TITLE='Company'

CAPTION=!quote(!k1).

 

!DOEND

!DOEND

!ENDDEFINE.

 

SET MPRINT ON.

 

 

mytab CAT= 2 3 4 /capt = 'Q2' 'Q3' 'Q4'.

 

Now with this syntax in each table I see "Q2 Q3 Q4" instead of the proper

quarter.

 

Is it possible to obtain what I'm looking for?

 

Thanks                                                 

 

 

 

 

--

View this message in context: http://spssx-discussion.1045642.n5.nabble.com/putting-a-caption-in-tables-through-a-macro-tp5729160.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

[progster]

Hi Manoj, thanks but the code that you suggest generate 9 tables, while it should be simply 3 tables, one for each quarter

[Manoj Arora (DEL/Abacus Analytics)]

Hi,

As per the syntax it will generate 9 tables because you have rotated 2 loops, one for I and another for j (3*3).

Thanks
Manoj


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of progster
Sent: Tuesday, April 07, 2015 4:24 PM
To: [hidden email]
Subject: Re: putting a caption in tables through a macro

Hi Manoj, thanks but the code that you suggest generate 9 tables, while it should be simply 3 tables, one for each quarter



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/putting-a-caption-in-tables-through-a-macro-tp5729160p5729162.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

[Jignesh Sutar]

If the second parameter (CAPT) is simply "Q" + quarter number (which is obtained from the first parameter (CAT)), then it is more less redundant as you can build this concatenation into the macro itself and removing the extra burden of this parameter.

By the way, will you dataset only contain a single years worth data? Else, you'll be aggregating quarters from different year periods (so you may want to take into account year)?



set mprint off.
define mytab (quarters=!cmdend).
!do !i !in (!quarters)

temp.
select if quarter=!i.
CTABLES
  /VLABELS VARIABLES=m1 DISPLAY=LABEL
  /TABLE m1 [C][COUNT F40.0, COLPCT.COUNT PCT40.0]
  /CATEGORIES VARIABLES=m1 ORDER=D KEY=COUNT EMPTY=include TOTAL=YES POSITION=AFTER
  /TITLES TITLE='Company' CAPTION=!quote(!concat("Q",!i)).

!doend
!enddefine.

 

set mprint on.
mytab quarters= 2 3 4.
set mprint off.

[David Marso]

Indeed Manoj!!

OP: Need one loop (NOT 2 and pay attention to what your own code is doing DOH!!!),

Solutions:
1. use !HEAD and !TAIL to parse second parameter.
2. search this group for Recursion.
Simple example template, leaving it to OP to plug in the specifics.

DEFINE !rec (!POS !CHAREND("/") / !POS !CMDEND )
!IF (!HEAD(!1) !NE !NULL ) !THEN
ECHO !QUOTE(!HEAD(!1)).
ECHO !QUOTE(!HEAD(!2)).
!rec !TAIL(!1) / !TAIL(!2) .
!IFEND
!ENDDEFINE .
!rec list1 / list2 .

Manoj Arora (DEL/Abacus Analytics) wrote

Hi,

As per the syntax it will generate 9 tables because you have rotated 2 loops, one for I and another for j (3*3).

Thanks
Manoj


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of progster
Sent: Tuesday, April 07, 2015 4:24 PM
To: [hidden email]
Subject: Re: putting a caption in tables through a macro

Hi Manoj, thanks but the code that you suggest generate 9 tables, while it should be simply 3 tables, one for each quarter



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/putting-a-caption-in-tables-through-a-macro-tp5729160p5729162.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