Output from a TABLE

[Sean McKenzie]

Below is my first real production program (yippie).  I am reading my real
data from my real database, perfromaing bunch of transforamtions and now I
am beginning my calculations.  All I need to do is the same thing for a
bunch more variables and a bunch more datasets.  I could probably improve a
bunch of things, exp. the way I access my deatabse (the sql below reads from
an existing query, which unless I can put that query inside of the program I
will need to first change the query each time I run the program...)....

OK so Iget to the point where I count my beans....

FILTER BY billkid.
FREQUENCIES OfficeName.

Which Looks like this, (and is correct BTW as I have already generated this
data using EXCEL and ACCESS).

OfficeName
                Frequency       Percent Valid Percent   Cumulative Percent
Valid   ANCHORAGE CHILD CARE    1       .0      .0      .0
        CATHOLIC COMM. SVC.     529     9.4     9.4     9.4
        CORDOVA CHILD CARE      11      .2      .2      9.6
        FNSB CHILD CARE ASST    539     9.5     9.5     19.1
        HAINES/SKAGWAY  17      .3      .3      19.4
        HOMER CHILD CARE        97      1.7     1.7     21.2
        KENAI CHILD CARE        433     7.7     7.7     28.8
        KODIAK CHILD CARE       97      1.7     1.7     30.5
        MAT SU CHILD CARE       605     10.7    10.7    41.3
        METLAKATLA CHILD CAR    33      .6      .6      41.8
        MUNICIPALITY OF ANCH    3061    54.2    54.2    96.1
        NOME, BARROW, KOTZ.,    15      .3      .3      96.3
        SEWARD CHILD CARE       44      .8      .8      97.1
        SITKA CHILD CARE        76      1.3     1.3     98.5
        VALDEZ CHILD CARE       41      .7      .7      99.2
        WRANGELL CHILD CARE     46      .8      .8      100.0
        Total   5645    100.0   100.0

What I need to be able to do is write data from the above table to specific
locations in a specific EXCEL SS.  Can anybody help?




GET DATA /TYPE=ODBC /CONNECT=
'DSN=MS Access'+
' Database;DBQ=H:\ChildCare\SMcKenzie\iccistous\dbtous090506.mdb;DriverId='+
'25;FIL=MS Access;MaxBufferSize=2048;'
  'PageTimeout=5;'
/SQL = 'SELECT  FamilyID,  OfficeCode,  OfficeName,  OfficeUnit, '+
' CCAWorkerID,  CCAWorkerName,  LocCommunity,  MailCity,  '
' MemberID,  RelationshipCode,  ProgramTypeCode, InvoiceMonth, '
' P1MemberName, ProgramType, DevelopDisabled, SpecialNeeds, '
' VerifyComment, DevelopDisabled, SpecialNeedsPct, SpecialNeedsAmt, '
' Relative, ProviderName, CCProvTypeCode, VerifyAuthAmt, VerifyRegFeeAmt, '
' VerifySupplAmt FROM  billsla'.

CACHE.
DATASET NAME DataSet1 WINDOW=FRONT.

STRING conc (A255) / type (A1).

IF (CCProvTypeCode="LC" OR CCProvTypeCode="MC") type="Z".
IF (CCProvTypeCode="LG") type="Y".
IF (CCProvTypeCode="LH" OR CCProvTypeCode="MH") type="X".
IF (CCProvTypeCode="AP") type="W".
IF (CCProvTypeCode="AR") type="V".
IF (CCProvTypeCode="AI") type="U".
IF (CCProvTypeCode="") type="A".

COMPUTE sum=VerifyAuthAmt+VerifyRegFeeAmt+VerifySupplAmt.

STRING sumstring sum1a sum2a sum3a (A60).


IF(sum - TRUNC(sum)=0) sum1 = sum.
IF(sum*10-TRUNC(sum*10)=0 AND sum - TRUNC(sum)<>0) sum2=sum.
IF(sum*10-TRUNC(sum*10)>0) sum3=sum.

FORMAT sum1 (F20.0) sum2 (f20.1) sum3(f20.2).

COMPUTE sum1a=string(sum1,f20.0).
COMPUTE sum2a=string(sum2,f20.1).
COMPUTE sum3a=string(sum3,f20.2).


COMPUTE
sumstring=RTRIM(LTRIM(concat(RTRIM(LTRIM(sum1a)),RTRIM(LTRIM(sum2a)),RTRIM(LTRIM(sum3a))),"."),".").


COMPUTE
conc=CONCCAT(FamilyID,MemberID,LTRIM(RTRIM(sumstring)),LTRIM(RTRIM(type)),ProgramTypeCode).

SORT CASES by conc (A).

STRING LFamilyID (A8) LMemberID (A10).

COMPUTE LFamilyID=LAG(FamilyID,1).
COMPUTE LMemberID=LAG(MemberID,1).

IF(FamilyID<>LFamilyID OR MemberID<>LMemberID) billkid=1.
IF(FamilyID=LFamilyID AND MemberID=LMemberID) billkid=0.

FILTER BY billkid.
FREQUENCIES OfficeName.

SAVE TRANSALTE /OUTFILE= 'h:\childcare\smckenzie\spss\outtest.xls'
/REPLACE
/TYPE=XLS
/VERSION=8
/FIELDNAMES
/KEEP= ALL.
EXECUTE.

[Hal 9000]

Sean, I am using a data-output and organizing strategy similar to yours. I
use save translate type = odbc table = 'excel_sheet_name' and I shoot
different aggregate files onto different worksheets...so the data is
there across many pages. Then, I create the pretty face for the report which
consists of tables and charts that reference cells and ranges from the
various worksheets with the data. If you will be re-using this template (the
only real scenario which makes this much effort worth it, in my opinion),
then you have to make sure the data that is output to Excel stays in exactly
the same structure...a problem when you are outputting aggregate data with
ethnicity as a break variable and this week there isn't a single Native
American! I developed a fix for this situation--let me know if it's relevant
info and I'll spell it out. Good Luck!

Gary


On 9/14/06, Sean McKenzie <[hidden email]> wrote:

>
> Below is my first real production program (yippie).  I am reading my real
> data from my real database, perfromaing bunch of transforamtions and now I
> am beginning my calculations.  All I need to do is the same thing for a
> bunch more variables and a bunch more datasets.  I could probably improve
> a
> bunch of things, exp. the way I access my deatabse (the sql below reads
> from
> an existing query, which unless I can put that query inside of the program
> I
> will need to first change the query each time I run the program...)....
>
> OK so Iget to the point where I count my beans....
>
> FILTER BY billkid.
> FREQUENCIES OfficeName.
>
> Which Looks like this, (and is correct BTW as I have already generated
> this
> data using EXCEL and ACCESS).
>
> OfficeName
>                Frequency       Percent Valid Percent   Cumulative Percent
> Valid   ANCHORAGE CHILD CARE    1       .0      .0      .0
>        CATHOLIC COMM. SVC.     529     9.4     9.4     9.4
>        CORDOVA CHILD CARE      11      .2      .2      9.6
>        FNSB CHILD CARE ASST    539     9.5     9.5     19.1
>        HAINES/SKAGWAY  17      .3      .3      19.4
>        HOMER CHILD CARE        97      1.7     1.7     21.2
>        KENAI CHILD CARE        433     7.7     7.7     28.8
>        KODIAK CHILD CARE       97      1.7     1.7     30.5
>        MAT SU CHILD CARE       605     10.7    10.7    41.3
>        METLAKATLA CHILD CAR    33      .6      .6      41.8
>        MUNICIPALITY OF ANCH    3061    54.2    54.2    96.1
>        NOME, BARROW, KOTZ.,    15      .3      .3      96.3
>        SEWARD CHILD CARE       44      .8      .8      97.1
>        SITKA CHILD CARE        76      1.3     1.3     98.5
>        VALDEZ CHILD CARE       41      .7      .7      99.2
>        WRANGELL CHILD CARE     46      .8      .8      100.0
>        Total   5645    100.0   100.0
>
> What I need to be able to do is write data from the above table to
> specific
> locations in a specific EXCEL SS.  Can anybody help?
>
>
>
>
> GET DATA /TYPE=ODBC /CONNECT=
> 'DSN=MS Access'+
> '
> Database;DBQ=H:\ChildCare\SMcKenzie\iccistous\dbtous090506.mdb;DriverId='+
> '25;FIL=MS Access;MaxBufferSize=2048;'
> 'PageTimeout=5;'
> /SQL = 'SELECT  FamilyID,  OfficeCode,  OfficeName,  OfficeUnit, '+
> ' CCAWorkerID,  CCAWorkerName,  LocCommunity,  MailCity,  '
> ' MemberID,  RelationshipCode,  ProgramTypeCode, InvoiceMonth, '
> ' P1MemberName, ProgramType, DevelopDisabled, SpecialNeeds, '
> ' VerifyComment, DevelopDisabled, SpecialNeedsPct, SpecialNeedsAmt, '
> ' Relative, ProviderName, CCProvTypeCode, VerifyAuthAmt, VerifyRegFeeAmt,
> '
> ' VerifySupplAmt FROM  billsla'.
>
> CACHE.
> DATASET NAME DataSet1 WINDOW=FRONT.
>
> STRING conc (A255) / type (A1).
>
> IF (CCProvTypeCode="LC" OR CCProvTypeCode="MC") type="Z".
> IF (CCProvTypeCode="LG") type="Y".
> IF (CCProvTypeCode="LH" OR CCProvTypeCode="MH") type="X".
> IF (CCProvTypeCode="AP") type="W".
> IF (CCProvTypeCode="AR") type="V".
> IF (CCProvTypeCode="AI") type="U".
> IF (CCProvTypeCode="") type="A".
>
> COMPUTE sum=VerifyAuthAmt+VerifyRegFeeAmt+VerifySupplAmt.
>
> STRING sumstring sum1a sum2a sum3a (A60).
>
>
> IF(sum - TRUNC(sum)=0) sum1 = sum.
> IF(sum*10-TRUNC(sum*10)=0 AND sum - TRUNC(sum)<>0) sum2=sum.
> IF(sum*10-TRUNC(sum*10)>0) sum3=sum.
>
> FORMAT sum1 (F20.0) sum2 (f20.1) sum3(f20.2).
>
> COMPUTE sum1a=string(sum1,f20.0).
> COMPUTE sum2a=string(sum2,f20.1).
> COMPUTE sum3a=string(sum3,f20.2).
>
>
> COMPUTE
>
> sumstring=RTRIM(LTRIM(concat(RTRIM(LTRIM(sum1a)),RTRIM(LTRIM(sum2a)),RTRIM(LTRIM(sum3a))),"."),".").
>
>
> COMPUTE
>
> conc=CONCCAT(FamilyID,MemberID,LTRIM(RTRIM(sumstring)),LTRIM(RTRIM(type)),ProgramTypeCode).
>
> SORT CASES by conc (A).
>
> STRING LFamilyID (A8) LMemberID (A10).
>
> COMPUTE LFamilyID=LAG(FamilyID,1).
> COMPUTE LMemberID=LAG(MemberID,1).
>
> IF(FamilyID<>LFamilyID OR MemberID<>LMemberID) billkid=1.
> IF(FamilyID=LFamilyID AND MemberID=LMemberID) billkid=0.
>
> FILTER BY billkid.
> FREQUENCIES OfficeName.
>
> SAVE TRANSALTE /OUTFILE= 'h:\childcare\smckenzie\spss\outtest.xls'
> /REPLACE
> /TYPE=XLS
> /VERSION=8
> /FIELDNAMES
> /KEEP= ALL.
> EXECUTE.
>