SPSSX Discussion

Macro

Classic

List

Threaded

4 messages Options

Nandini-2

Macro

I'm trying to create a macro to read monthly data from Jan 2004 to Feb
2005 from a datawarehouse and save each month's data in a separate file.
The dates (month and year) are to be determined from a variable clsng_dt
(dd/mm/yyyy). What is the best way to do this?

This is what I have so far:

DEFINE DATASET () .
COMPUTE yr = XDATE.YEAR(clsng_dt) .
COMPUTE mn = XDATE.MONTH(clsng_mn) .
!let !dt = CONCAT!(!yr,!mn) .
!DO !dt = 0104 !to 0205 .

GET DATA
/TYPE=ODBC
/CONNECT='DSN=huddw_iq12;uid=h22298;prows=15000;pbuf=1024'
/SQL ='SELECT '
' T1.case_nbr, '
' T1.clm_typ, '
' T1.clsng_dt, '
' year(clsng_dt) AS clsng_yr, '
' month(clsng_dt) AS clsng_mn, '
' T1.soa_cd, '
'FROM ((dbo.database_1 AS T1 '
'WHERE soa_cd <> ''255'' '
.
CACHE.
EXECUTE.

SAVE OUTFILE = 'C:\Desktop\Temp\data_!dt.sav' .

!DOEND .
!ENDDEFINE .

Thanks for your help.

ViAnn Beadle

Re: Macro

Is there some reason why you want separate files here? IMHO, putting the
records in one file makes them much more manageable, especially if you're
going to transform data within SPSS and run analyses. You could either split
file or filter on a yearmonth variable for separate analyses.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Nan
Sent: Wednesday, July 11, 2007 2:32 PM
To: [hidden email]
Subject: Macro

I'm trying to create a macro to read monthly data from Jan 2004 to Feb
2005 from a datawarehouse and save each month's data in a separate file.
The dates (month and year) are to be determined from a variable clsng_dt
(dd/mm/yyyy). What is the best way to do this?

This is what I have so far:

DEFINE DATASET () .
COMPUTE yr = XDATE.YEAR(clsng_dt) .
COMPUTE mn = XDATE.MONTH(clsng_mn) .
!let !dt = CONCAT!(!yr,!mn) .
!DO !dt = 0104 !to 0205 .

GET DATA
/TYPE=ODBC
/CONNECT='DSN=huddw_iq12;uid=h22298;prows=15000;pbuf=1024'
/SQL ='SELECT '
' T1.case_nbr, '
' T1.clm_typ, '
' T1.clsng_dt, '
' year(clsng_dt) AS clsng_yr, '
' month(clsng_dt) AS clsng_mn, '
' T1.soa_cd, '
'FROM ((dbo.database_1 AS T1 '
'WHERE soa_cd <> ''255'' '
.
CACHE.
EXECUTE.

SAVE OUTFILE = 'C:\Desktop\Temp\data_!dt.sav' .

!DOEND .
!ENDDEFINE .

Thanks for your help.

Albert-Jan Roskam

Re: Macro

If you have a really large number of records per
month, it ight be more practical to save the files per
month.

You are using COMPUTE before you have data. I think
you need a CAST (or CONVERT) statement in the SQL
syntax to extract the right date elements.
http://msdn2.microsoft.com/en-us/library/ms187928.aspx
If I were you, I'd first attempt to make the basic
syntax work, and then make a macro out of it.

The !DO loop won't work this way. You probably need to
loops, one for year, and one for month.
!DO !YR = 1 !TO 2.
!DO !MN = 1 !TO 12.
** sql connect syntax.
!DOEND.
!DOEND.

Furthermore, I wouldn't use 'dataset' a macro name. It
sounds to much like a native SPSS command.

If the number of files to be saved is not too large (I
believe it's 40), it's more effecient to use XSAVE.

XSAVE OUTFILE = !QUOTE(!CONCAT
('C:\Desktop\Temp\data_',!dt,'.sav')) .
!DOEND.
!DOEND.
EXE.
!ENDDEFINE.

Good luck!

Albert-Jan

--- ViAnn Beadle <[hidden email]> wrote:

> Is there some reason why you want separate files
> here? IMHO, putting the
> records in one file makes them much more manageable,
> especially if you're
> going to transform data within SPSS and run
> analyses. You could either split
> file or filter on a yearmonth variable for separate
> analyses.
>
> -----Original Message-----
> From: SPSSX(r) Discussion
> [mailto:[hidden email]] On Behalf Of Nan
> Sent: Wednesday, July 11, 2007 2:32 PM
> To: [hidden email]
> Subject: Macro
>
> I'm trying to create a macro to read monthly data
> from Jan 2004 to Feb
> 2005 from a datawarehouse and save each month's data
> in a separate file.
> The dates (month and year) are to be determined from
> a variable clsng_dt
> (dd/mm/yyyy). What is the best way to do this?
>
> This is what I have so far:
>
> DEFINE DATASET () .
> COMPUTE yr = XDATE.YEAR(clsng_dt) .
> COMPUTE mn = XDATE.MONTH(clsng_mn) .
> !let !dt = CONCAT!(!yr,!mn) .
> !DO !dt = 0104 !to 0205 .
>
> GET DATA
> /TYPE=ODBC
>

/CONNECT='DSN=huddw_iq12;uid=h22298;prows=15000;pbuf=1024'

> /SQL ='SELECT '
> ' T1.case_nbr, '
> ' T1.clm_typ, '
> ' T1.clsng_dt, '
> ' year(clsng_dt) AS clsng_yr, '
> ' month(clsng_dt) AS clsng_mn, '
> ' T1.soa_cd, '
> 'FROM ((dbo.database_1 AS T1 '
> 'WHERE soa_cd <> ''255'' '
> .
> CACHE.
> EXECUTE.
>
> SAVE OUTFILE = 'C:\Desktop\Temp\data_!dt.sav' .
>
> !DOEND .
> !ENDDEFINE .
>
> Thanks for your help.
>

Cheers!
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Did you know that 87.166253% of all statistics claim a precision of results that is not justified by the method employed? [HELMUT RICHTER]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

____________________________________________________________________________________
Need a vacation? Get great deals
to amazing places on Yahoo! Travel.
http://travel.yahoo.com/

Marks, Jim

Re: Macro

XSAVE is more efficient, but only can be used for up to 10 files.

--jim

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Albert-jan Roskam
Sent: Thursday, July 12, 2007 3:24 AM
To: [hidden email]
Subject: Re: Macro

If you have a really large number of records per month, it ight be more
practical to save the files per month.

You are using COMPUTE before you have data. I think you need a CAST (or
CONVERT) statement in the SQL syntax to extract the right date elements.
http://msdn2.microsoft.com/en-us/library/ms187928.aspx
If I were you, I'd first attempt to make the basic syntax work, and then
make a macro out of it.

The !DO loop won't work this way. You probably need to loops, one for
year, and one for month.
!DO !YR = 1 !TO 2.
!DO !MN = 1 !TO 12.
** sql connect syntax.
!DOEND.
!DOEND.

Furthermore, I wouldn't use 'dataset' a macro name. It sounds to much
like a native SPSS command.

If the number of files to be saved is not too large (I believe it's 40),
it's more effecient to use XSAVE.

XSAVE OUTFILE = !QUOTE(!CONCAT
('C:\Desktop\Temp\data_',!dt,'.sav')) .
!DOEND.
!DOEND.
EXE.
!ENDDEFINE.

Good luck!

Albert-Jan

--- ViAnn Beadle <[hidden email]> wrote:

> Is there some reason why you want separate files here? IMHO, putting
> the records in one file makes them much more manageable, especially if

> you're going to transform data within SPSS and run analyses. You could

> either split file or filter on a yearmonth variable for separate
> analyses.
>
> -----Original Message-----
> From: SPSSX(r) Discussion
> [mailto:[hidden email]] On Behalf Of Nan
> Sent: Wednesday, July 11, 2007 2:32 PM
> To: [hidden email]
> Subject: Macro
>
> I'm trying to create a macro to read monthly data from Jan 2004 to Feb
> 2005 from a datawarehouse and save each month's data in a separate
> file.
> The dates (month and year) are to be determined from a variable
> clsng_dt (dd/mm/yyyy). What is the best way to do this?
>
> This is what I have so far:
>
> DEFINE DATASET () .
> COMPUTE yr = XDATE.YEAR(clsng_dt) .
> COMPUTE mn = XDATE.MONTH(clsng_mn) .
> !let !dt = CONCAT!(!yr,!mn) .
> !DO !dt = 0104 !to 0205 .
>
> GET DATA
> /TYPE=ODBC
>

/CONNECT='DSN=huddw_iq12;uid=h22298;prows=15000;pbuf=1024'

Cheers!
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Did you know that 87.166253% of all statistics claim a precision of
results that is not justified by the method employed? [HELMUT RICHTER]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

________________________________________________________________________
____________
Need a vacation? Get great deals
to amazing places on Yahoo! Travel.
http://travel.yahoo.com/