Creating many time series from a single ODBC sql call

[jjccia@yahoo.com]

Hi everyone,

My name is Joe and I am a newb to Eviews. I work for a railroad company in the Chicago area. I am currently working on a project where I will be pulling several thousand records with an ODBC SQL call to MS Sql Server. I have gotten the SQL call to work via ODBC but am unsure on how to go about making many different unique time series out of one (and not 100) sql calls. It's kind of complicated so I wrote a description and put it in a word doc attached to this post. The doc has pictures in it and I could not figure out how to embed pictures here so I put it all in the attached doc. Anyone who could take a look at the problem I am facing in the attached word doc I would be extremely grateful to.

thanks very much!

Joe

[jthodge]

It sounds like what you want to do is unstack your dataset. For the workfile created by your SQL query, I think the following code may do the job:

Code: Select all

alpha varname = commodity + origin pageunstack(namepat=*_?) varname yrqtr @ teus rename teus_* *

Here's a description of what the code is doing:
(1) Define a new alpha series (say "Varname") that concatenates your Commodity and Origin series together.
(2) Unstack the Teus series onto a new page using Varname to indicate the new series names and YrQtr to identify the observations in your new dataset.
(3) Eviews includes "Teus" in the new series names. You can get rid of this extra labeling by renaming the new series (the wildcard character allows you to do them all at once).

[jjccia@yahoo.com]

Thank you! I appreciate the reply! A little of what you are saying is going over my head some but I think I get it. At any rate, I did find a way to do it, which is essentially completely SQL based. I may redo it using your method because it sounds like a shorter and more natural way to do it. In the meantime, I do have another question, regarding how I did it with the SQL statement. Here is the code I wrote shown below. It is a long SQL case statement with a group by. I know it looks bad but it was not really that hard to write, as I have well defined commodities and regions. But here is my question. At the very end of this code I have:

@freq q 1991:1 @smpl @all

which is starting the series at the first quarter of 1991. The data goes to 2013. I want to be able to continue each variable past the end of the 2013 time frame with dummy variables, all the way out to 2029 quarter 4. I tried coding:

@freq q 1991:1 2029:4 @smpl @all

but I got a syntax error. I know there are options on the Proc menu for 'structure/resize current page" to change the end date, which will add my future dummy variables, but is there any way to do this programmatically with a line like this?

@freq q 1991:1 2029:4 @smpl @all

I'm trying to set it up so the end user doesn't have to further modify the series and can just take what I give them. the whole statement is shown below. thanks again for your help.

Joe


wfopen(link, type=odbc) DSN=PELAGIC;Description=PIERS;UID=DEVJJC;Trusted_Connection=Yes;APP=EViews;WSID=LT4077; "select YRQTR, SUM(AOASIA) AS AOASIA, SUM(AOEUR) AS AOEUR, SUM(AOLAM) AS AOLAM, SUM(AOOTH) AS AOOTH, SUM(IMASIA) AS IMASIA, SUM(IMEUR) AS IMEUR, SUM(IMLAM) AS IMLAM, SUM(IMOTH) AS IMOTH, SUM(APASIA) AS APASIA, SUM(APEUR) AS APEUR, SUM(APLAM) AS APLAM, SUM(APOTH) AS APOTH, SUM(AUASIA) AS AUASIA, SUM(AUEUR) AS AUEUR, SUM(AULAM) AS AULAM, SUM(AUOTH) AS AUOTH, SUM(ELASIA) AS ELASIA, SUM(ELEUR) AS ELEUR, SUM(ELLAM) AS ELLAM, SUM(ELOTH) AS ELOTH, SUM(FDASIA) AS FDASIA, SUM(FDEUR) AS FDEUR, SUM(FDLAM) AS FDLAM, SUM(FDOTH) AS FDOTH, SUM(FTASIA) AS FTASIA, SUM(FTEUR) AS FTEUR, SUM(FTLAM) AS FTLAM, SUM(FTOTH) AS FTOTH, SUM(FSASIA) AS FSASIA, SUM(FSEUR) AS FSEUR, SUM(FSLAM) AS FSLAM, SUM(FSOTH) AS FSOTH, SUM(FNASIA) AS FNASIA, SUM(FNEUR) AS FNEUR, SUM(FNLAM) AS FNLAM, SUM(FNOTH) AS FNOTH, SUM(HAASIA) AS HAASIA, SUM(HAEUR) AS HAEUR, SUM(HALAM) AS HALAM, SUM(HAOTH) AS HAOTH, SUM(HMASIA) AS HMASIA, SUM(HMEUR) AS HMEUR, SUM(HMLAM) AS HMLAM, SUM(HMOTH) AS HMOTH, SUM(MNASIA) AS MNASIA, SUM(MNEUR) AS MNEUR, SUM(MNLAM) AS MNLAM, SUM(MNOTH) AS MNOTH, SUM(OEASIA) AS OEASIA, SUM(OEEUR) AS OEEUR, SUM(OELAM) AS OELAM, SUM(OEOTH) AS OEOTH, SUM(TEXASIA) AS TEXASIA, SUM(TEXEUR) AS TEXEUR, SUM(TEXLAM) AS TEXLAM, SUM(TEXOTH) AS TEXOTH, SUM(TRASIA) AS TRASIA, SUM(TREUR) AS TREUR, SUM(TRLAM) AS TRLAM, SUM(TROTH) AS TROTH, SUM(TYASIA) AS TYASIA, SUM(TYEUR) AS TYEUR, SUM(TYLAM) AS TYLAM, SUM(TYOTH) AS TYOTH from ( select YRQTR, case when commodity='AO' and Origin='Asia' then TEUS else null end as AOASIA, case when commodity='AO' and Origin='Eur' then TEUS else null end as AOEUR, case when commodity='AO' and Origin='Lam' then TEUS else null end as AOLAM, case when commodity='AO' and Origin='Other' then TEUS else null end as AOOTH, case when commodity='IM' and Origin='Asia' then TEUS else null end as IMASIA, case when commodity='IM' and Origin='Eur' then TEUS else null end as IMEUR, case when commodity='IM' and Origin='Lam' then TEUS else null end as IMLAM, case when commodity='IM' and Origin='Other' then TEUS else null end as IMOTH, case when commodity='AP' and Origin='Asia' then TEUS else null end as APASIA, case when commodity='AP' and Origin='Eur' then TEUS else null end as APEUR, case when commodity='AP' and Origin='Lam' then TEUS else null end as APLAM, case when commodity='AP' and Origin='Other' then TEUS else null end as APOTH, case when commodity='AU' and Origin='Asia' then TEUS else null end as AUASIA, case when commodity='AU' and Origin='Eur' then TEUS else null end as AUEUR, case when commodity='AU' and Origin='Lam' then TEUS else null end as AULAM, case when commodity='AU' and Origin='Other' then TEUS else null end as AUOTH, case when commodity='EL' and Origin='Asia' then TEUS else null end as ELASIA, case when commodity='EL' and Origin='Eur' then TEUS else null end as ELEUR, case when commodity='EL' and Origin='Lam' then TEUS else null end as ELLAM, case when commodity='EL' and Origin='Other' then TEUS else null end as ELOTH, case when commodity='FD' and Origin='Asia' then TEUS else null end as FDASIA, case when commodity='FD' and Origin='Eur' then TEUS else null end as FDEUR, case when commodity='FD' and Origin='Lam' then TEUS else null end as FDLAM, case when commodity='FD' and Origin='Other' then TEUS else null end as FDOTH, case when commodity='FT' and Origin='Asia' then TEUS else null end as FTASIA, case when commodity='FT' and Origin='Eur' then TEUS else null end as FTEUR, case when commodity='FT' and Origin='Lam' then TEUS else null end as FTLAM, case when commodity='FT' and Origin='Other' then TEUS else null end as FTOTH, case when commodity='FS' and Origin='Asia' then TEUS else null end as FSASIA, case when commodity='FS' and Origin='Eur' then TEUS else null end as FSEUR, case when commodity='FS' and Origin='Lam' then TEUS else null end as FSLAM, case when commodity='FS' and Origin='Other' then TEUS else null end as FSOTH, case when commodity='FN' and Origin='Asia' then TEUS else null end as FNASIA, case when commodity='FN' and Origin='Eur' then TEUS else null end as FNEUR, case when commodity='FN' and Origin='Lam' then TEUS else null end as FNLAM, case when commodity='FN' and Origin='Other' then TEUS else null end as FNOTH, case when commodity='HA' and Origin='Asia' then TEUS else null end as HAASIA, case when commodity='HA' and Origin='Eur' then TEUS else null end as HAEUR, case when commodity='HA' and Origin='Lam' then TEUS else null end as HALAM, case when commodity='HA' and Origin='Other' then TEUS else null end as HAOTH, case when commodity='HM' and Origin='Asia' then TEUS else null end as HMASIA, case when commodity='HM' and Origin='Eur' then TEUS else null end as HMEUR, case when commodity='HM' and Origin='Lam' then TEUS else null end as HMLAM, case when commodity='HM' and Origin='Other' then TEUS else null end as HMOTH, case when commodity='MN' and Origin='Asia' then TEUS else null end as MNASIA, case when commodity='MN' and Origin='Eur' then TEUS else null end as MNEUR, case when commodity='MN' and Origin='Lam' then TEUS else null end as MNLAM, case when commodity='MN' and Origin='Other' then TEUS else null end as MNOTH, case when commodity='OE' and Origin='Asia' then TEUS else null end as OEASIA, case when commodity='OE' and Origin='Eur' then TEUS else null end as OEEUR, case when commodity='OE' and Origin='Lam' then TEUS else null end as OELAM, case when commodity='OE' and Origin='Other' then TEUS else null end as OEOTH, case when commodity='TEX' and Origin='Asia' then TEUS else null end as TEXASIA, case when commodity='TEX' and Origin='Eur' then TEUS else null end as TEXEUR, case when commodity='TEX' and Origin='Lam' then TEUS else null end as TEXLAM, case when commodity='TEX' and Origin='Other' then TEUS else null end as TEXOTH, case when commodity='TR' and Origin='Asia' then TEUS else null end as TRASIA, case when commodity='TR' and Origin='Eur' then TEUS else null end as TREUR, case when commodity='TR' and Origin='Lam' then TEUS else null end as TRLAM, case when commodity='TR' and Origin='Other' then TEUS else null end as TROTH, case when commodity='TY' and Origin='Asia' then TEUS else null end as TYASIA, case when commodity='TY' and Origin='Eur' then TEUS else null end as TYEUR, case when commodity='TY' and Origin='Lam' then TEUS else null end as TYLAM, case when commodity='TY' and Origin='Other' then TEUS else null end as TYOTH from USER_AREA.dbo.PIERS_Imports_Summary ) innersel group by yrqtr order by yrqtr" @freq q 1991:1 @smpl @all