full data merge using new query wizard

[Dittfurth, Monica]

This might be a really simple question, so figured I would just ask the group…

 

I want to use the Data Query Wizard to bring in an excel workbook and then merge the data on two of the spreadsheets. But, I want a ‘full’ join instead of a left join. I tried changing this in the syntax, but I get an error message.

I’m familiar with SAS and SPSS but not that great with sql.

 

 

GET DATA

  /TYPE=ODBC

  /CONNECT='DSN=Excel Files;DBQ=F:\SPSS\Database Query.xlsx;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;'

  /SQL='SELECT T0.NAME, T0.Var1, T0.Var2, T0.Var3, T1.NAME AS NAME1, T1.Var1 AS Var11, T1.Var4b FROM { oj `Sheet2$` T1 LEFT OUTER JOIN `Sheet1$` T0 ON T1.NAME = T0.NAME }'

  /ASSUMEDSTRWIDTH=255.

CACHE.

EXECUTE.

DATASET NAME DataSet1 WINDOW=FRONT.

 

 

I know there are other ways of merging the data. I just want to see if there was a quicker way to do it with the wizard tool so that I can provide it as a tip for those around me that aren’t as good at using syntax.

 

Any help would be greatly appreciated!

 

Monica

 

 

[Albert-Jan Roskam]

Hi,

I have never used this approach, but you could try to use the UNION of two SELECT queries, ie.

SELECT * FROM 'sheet1$'
UNION
SELECT * FROM 'sheet2$'
;

Always a bit tricky to get the quoting right. Make sure the inner and the outer quotings differ. It seems that fancy quotes are used as inner quotes in  your code example.

Cheers!!
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---

From: Monica Dittfurth <[hidden email]>
To: [hidden email]
Sent: Thursday, November 17, 2011 3:55 AM
Subject: [SPSSX-L] full data merge using new query wizard

This might be a really simple question, so figured I would just ask the group…

 

I want to use the Data Query Wizard to bring in an excel workbook and then merge the data on two of the spreadsheets. But, I want a ‘full’ join instead of a left join. I tried changing this in the syntax, but I get an error message.

I’m familiar with SAS and SPSS but not that great with sql.

 

 

GET DATA

  /TYPE=ODBC

  /CONNECT='DSN=Excel Files;DBQ=F:\SPSS\Database Query.xlsx;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;'

  /SQL='SELECT T0.NAME, T0.Var1, T0.Var2, T0.Var3, T1.NAME AS NAME1, T1.Var1 AS Var11, T1.Var4b FROM { oj `Sheet2$` T1 LEFT OUTER JOIN `Sheet1$` T0 ON T1.NAME = T0.NAME }'

  /ASSUMEDSTRWIDTH=255.

CACHE.

EXECUTE.

DATASET NAME DataSet1 WINDOW=FRONT.

 

 

I know there are other ways of merging the data. I just want to see if there was a quicker way to do it with the wizard tool so that I can provide it as a tip for those around me that aren’t as good at using syntax.

 

Any help would be greatly appreciated!

 

Monica