export variable as matrix (ASCII)

[SPSS-user-Austria]

Hello everybody,

I am new here and I hope you can help me concerning the "problem" stated below.

At the moment I try to learn the SPSS syntax more effectively as it offers more functions than the GUI. Now I have one little problem which I cannot solve on my own and so I hope that you can help me as it seems to be very trivial.
I deal with big geodata sets in SPSS for the statistical analyses and I want to export certain variables afterwards as Matrix (ASCII-file). So I can import these files in a GIS-System for visualizing geodata (e.g. elevation).

In Detail:
From hundreds of variables with more than 180.000 cases I want to export only ONE variable in a new table as matrix (later saved as ASCII) with a user defined dimension. In MATLAB for instance, this function is very easy (Reshape) when converting a vector in a matrix with x rows and y columns. You only have to define which variable you want to convert in a matrix and what the dimension of the matrix looks like.

In SPSS I know that there is the MATRIX syntax with various subcommands but I did not find the solution until now. So I hope you can help me.

For a better understanding look here:

example for the existing dataset:
VAR1    VAR2    VAR3...
1            7            13
2            8            14
3            9            15
4            10          16
5            11          17
6            12          18

example for the new matrix with 2 columns and 3 rows (source vector is VAR1).
1    2
3    4
5    6

I started with following syntax but I do not know which subcommand is missing for defining the dimension of the new matrix. Have you any suggestions?

matrix.
get x /variables VAR1.
print x.
end matrix.

Thank you in advance and best regards from Innsbruck (Austria),
Holger

[David Marso]

"In MATLAB for instance, this function is very easy (Reshape)"
In SPSS this function is very easy (RESHAPE)...
From the FM:
"RESHAPE(M,S1,S2) Matrix of different dimensions. Takes three arguments, a matrix and
two scalars, whose product must equal the number of elements in the
matrix. Returns a matrix whose dimensions are given by the scalar arguments.
For example, if M is any matrix with exactly 50 elements,
then RESHAPE(M, 5, 10) is a matrix with 5 rows and 10 columns.
Elements are assigned to the reshaped matrix in order by row."
HTH, David
---

SPSS-user-Austria wrote

Hello everybody,

I am new here and I hope you can help me concerning the "problem" stated below.

At the moment I try to learn the SPSS syntax more effectively as it offers more functions than the GUI. Now I have one little problem which I cannot solve on my own and so I hope that you can help me as it seems to be very trivial.
I deal with big geodata sets in SPSS for the statistical analyses and I want to export certain variables afterwards as Matrix (ASCII-file). So I can import these files in a GIS-System for visualizing geodata (e.g. elevation).

In Detail:
From hundreds of variables with more than 180.000 cases I want to export only ONE variable in a new table as matrix (later saved as ASCII) with a user defined dimension. In MATLAB for instance, this function is very easy (Reshape) when converting a vector in a matrix with x rows and y columns. You only have to define which variable you want to convert in a matrix and what the dimension of the matrix looks like.

In SPSS I know that there is the MATRIX syntax with various subcommands but I did not find the solution until now. So I hope you can help me.

For a better understanding look here:

example for the existing dataset:
VAR1    VAR2    VAR3...
1            7            13
2            8            14
3            9            15
4            10          16
5            11          17
6            12          18

example for the new matrix with 2 columns and 3 rows (source vector is VAR1).
1    2
3    4
5    6

I started with following syntax but I do not know which subcommand is missing for defining the dimension of the new matrix. Have you any suggestions?

matrix.
get x /variables VAR1.
print x.
end matrix.

Thank you in advance and best regards from Innsbruck (Austria),
Holger

[SPSS-user-Austria]

Hey David,

thanks for your fast response!

I found this command in the Syntax Reference too but I still do not know how I have to embed this command in the syntax!

I started like this but it does not work..

matrix.
get x /variables VAR1.
Reshape(x,3,2).     ***3 for the number of rows and 2 for the numbers of columns in the new matrix
print x.
end matrix.

What do I do still wrong?

Thanks in advance!

[David Marso]

COMPUTE newmatrix=RESHAPE(x,nrows,ncolumns).
See WRITE statement for creating the ASCII file:
In fact you could do this in two steps.
MATRIX.
GET X /VARIABLES VAR1.
WRITE RESHAPE(x,nrows,ncols)
        / OUTFILE somevalidfilespec
        / FIELD begincol TO endcol BY increment (RTFM for specifics).
END MATRIX.

SPSS-user-Austria wrote

Hey David,

thanks for your fast response!

I found this command in the Syntax Reference too but I still do not know how I have to embed this command in the syntax!

I started like this but it does not work..

matrix.
get x /variables VAR1.
Reshape(x,3,2).     ***3 for the number of rows and 2 for the numbers of columns in the new matrix
print x.
end matrix.

What do I do still wrong?

Thanks in advance!

[SPSS-user-Austria]

Thanks a lot for your answers!

With the first command (Compute) I had no success?!

The second hint works but not satisfying..
Here it is (in case of my example)..

MATRIX.
GET X /VARIABLES VAR1.
WRITE RESHAPE(x,3,2)
        / OUTFILE=' D:\....\test\neu.txt'
        / FIELD = 1 TO 2 BY 2.
Print x. *for immediate correction in the SPSS-output
END MATRIX.

The output (now as .txt file) is still a columnvector, but why?
  1
  2
  3
  4
  5
  6

I assume that I did a mistake using the Field subcommand?!

Hope you can help me once again!
Thanks a million!

holger

 

[Bruce Weaver]

The "fine manual" entry for WRITE (under MATRIX - END MATRIX) is a bit short on examples, and the /FIELD sub-command is somewhat confusing.  But the following seems to work okay.

data list list / var1 to var3 (3f5.0).
begin data
1            7            13
2            8            14
3            9            15
4            10          16
5            11          17
6            12          18
end data.

MATRIX.
GET X /VARIABLES VAR1.
WRITE RESHAPE(x,3,2)
 /OUTFILE = 'C:\temp\neu.txt'
 /FIELD = 1 TO 10 BY 5
 /FORMAT = f5.0.
Print x.
Print RESHAPE(x,3,2).
END MATRIX.

Cheers,
Bruce

SPSS-user-Austria wrote

Thanks a lot for your answers!

With the first command (Compute) I had no success?!

The second hint works but not satisfying..
Here it is (in case of my example)..

MATRIX.
GET X /VARIABLES VAR1.
WRITE RESHAPE(x,3,2)
        / OUTFILE=' D:\....\test\neu.txt'
        / FIELD = 1 TO 2 BY 2.
Print x. *for immediate correction in the SPSS-output
END MATRIX.

The output (now as .txt file) is still a columnvector, but why?
  1
  2
  3
  4
  5
  6

I assume that I did a mistake using the Field subcommand?!

Hope you can help me once again!
Thanks a million!

holger

[David Marso]

Hi Holger,
I tested this one and it works.
The FIELD spec on WRITE refers to the width of the allocation to the text file rather than the columns in the resulting matrix.
MATRIX.
GET X / VARIABLES VAR1.
COMPUTE NEWVAR=RESHAPE(X,3,2).
PRINT  X.
PRINT NEWVAR.
WRITE (RESHAPE(x,3,2))
  /OUTFILE "C:\TEMP\test reshape.txt"
  /FIELD 1 TO 20 BY 10.
END MATRIX.
*contents of "..\test reshape.txt"
notice each element takes up 10 columns (20 total for the 2 elements).
--
      1.00      2.00
      3.00      4.00
      5.00      6.00

So after testing could be resolved to:
MATRIX.
GET X / VARIABLES VAR1.
WRITE (RESHAPE(x,3,2))   /OUTFILE "C:\TEMP\test reshape.txt"  /FIELD 1 TO 20 BY 10.
END MATRIX.

SPSS-user-Austria wrote

Thanks a lot for your answers!

With the first command (Compute) I had no success?!

The second hint works but not satisfying..
Here it is (in case of my example)..

MATRIX.
GET X /VARIABLES VAR1.
WRITE RESHAPE(x,3,2)
        / OUTFILE=' D:\....\test\neu.txt'
        / FIELD = 1 TO 2 BY 2.
Print x. *for immediate correction in the SPSS-output
END MATRIX.

The output (now as .txt file) is still a columnvector, but why?
  1
  2
  3
  4
  5
  6

I assume that I did a mistake using the Field subcommand?!

Hope you can help me once again!
Thanks a million!

holger

[SPSS-user-Austria]

Hi David and Bruce,

thanks for your help.

Your suggested syntax works definitely. Now I understand also the Field sub-command.

The only "disadvantage" of the Write command ist that you have to define the columns according to the digits which are used by the elements. If you have variable values with a different width (e.g. the variable contains the values 2, 133, 12512 etc.) you have to know the maximal width of the values in the variable you want to export. According to the maximal width you have to adjust your Field sub-command. In huge datasets this may be annoying because you have to look first to the maximum value.

Another "problem" is that you cannot define that the values in the outfile have to be separated by tab stop (I need a tab stop separated ASCI-file for the further import in my GIS-System). Actually the command Save Translate exists for that export definition but unfortunately it can not be used within the Matrix-End Matrix command.

I found out another resolution (-> restructuring) which works too and were I can define the tab stop separation. Here I have to create an auxiliary variable 'help' which contains the information to which row each values in the new file belongs to. Based on this variable I can restructure the table (casetovars). When saving the file as ASCII (*.dat) the auxiliary variable will be dropped. Here the only disadvantage is that you have to save the auxiliary variable together with the variable you want to export as ASCII in a NEW SPSS table (*.sav)..

Her is my alternative:

*Calculation of the case number
COMPUTE ID=$CASENUM.
EXECUTE.
VARIABLE LABELS ID 'case number'.

* Calculation of the auxiliary variable 'help'. This variable contains the information to which row each
* values belongs to. In this example 484 stands for the number of columns in the new matrix.

COMPUTE help=TRUNC((ID-1)/484)+1.
EXECUTE.

* restructuring of the new *sav-file which contains ONLY the auxiliary variable and the export variable
* (e.g. Var1)

CASESTOVARS
  /ID=help
  /GROUPBY=INDEX.

* save the restructured table as tabulator separated ASCII file (and drop the auxiliary variable)

SAVE TRANSLATE OUTFILE='C:\TEMP\export_as_matrix.dat'
  /TYPE=TAB
  /MAP
  /REPLACE
  /CELLS=VALUES
  /DROP=help.

There isn't just one way of doing it..

Cheers,
Holger

[David Marso]

"There isn't just one way of doing it.."
Nah... there are many ways to do most anything.. ;-)
Sometimes it is just necessary to be quite specific regarding exactly what you require.
The TAB delimited wrinkle was an essential piece of omitted information.
---
data list free / a b .
begin data
1 3    2 2    3 4    4 5    5 6    6 7
end data.
matrix.
get x / var a.
save reshape(x,3,2)/ outfile *.
end matrix.
save translate / outfile "C:\temp\blah Blah.dat" / type=tab.

SPSS-user-Austria wrote

Hi David and Bruce,

thanks for your help.

Your suggested syntax works definitely. Now I understand also the Field sub-command.

The only "disadvantage" of the Write command ist that you have to define the columns according to the digits which are used by the elements. If you have variable values with a different width (e.g. the variable contains the values 2, 133, 12512 etc.) you have to know the maximal width of the values in the variable you want to export. According to the maximal width you have to adjust your Field sub-command. In huge datasets this may be annoying because you have to look first to the maximum value.

Another "problem" is that you cannot define that the values in the outfile have to be separated by tab stop (I need a tab stop separated ASCI-file for the further import in my GIS-System). Actually the command Save Translate exists for that export definition but unfortunately it can not be used within the Matrix-End Matrix command.

I found out another resolution (-> restructuring) which works too and were I can define the tab stop separation. Here I have to create an auxiliary variable 'help' which contains the information to which row each values in the new file belongs to. Based on this variable I can restructure the table (casetovars). When saving the file as ASCII (*.dat) the auxiliary variable will be dropped. Here the only disadvantage is that you have to save the auxiliary variable together with the variable you want to export as ASCII in a NEW SPSS table (*.sav)..

Her is my alternative:

*Calculation of the case number
COMPUTE ID=$CASENUM.
EXECUTE.
VARIABLE LABELS ID 'case number'.

* Calculation of the auxiliary variable 'help'. This variable contains the information to which row each
* values belongs to. In this example 484 stands for the number of columns in the new matrix.

COMPUTE help=TRUNC((ID-1)/484)+1.
EXECUTE.

* restructuring of the new *sav-file which contains ONLY the auxiliary variable and the export variable
* (e.g. Var1)

CASESTOVARS
  /ID=help
  /GROUPBY=INDEX.

* save the restructured table as tabulator separated ASCII file (and drop the auxiliary variable)

SAVE TRANSLATE OUTFILE='C:\TEMP\export_as_matrix.dat'
  /TYPE=TAB
  /MAP
  /REPLACE
  /CELLS=VALUES
  /DROP=help.

There isn't just one way of doing it..

Cheers,
Holger

[SPSS-user-Austria]

David,

YOU are THE expert ;)

Perfect!

Many thanks,

Holger

[SPSS-user-Austria]

David (and others),

Your syntax is working perfect when I want to export single variables as tab delimited matrix in an outfile.

No I have one additional question:

Is it possible to write a header with a few rows in the outfile which does NOT depict the variable names?
This header is essential for importing the new file in my GIS-software. The outfile (with the header in the first six rows) has to look like this:

ncols         484
nrows         378
xllcorner     19920.980000001
yllcorner     248612.98
cellsize      50
NODATA_value  -9999
-9999 -9999 -9999 -9999 -9999 -9999 -9999
-9999 -9999 -9999 -9999 -9999 -9999 -9999
-9999 -9999 -9999 -9999 -9999 -9999 -9999
-9999 -9999 -9999 -9999 -9999 -9999 -9999

The header consist of six rows (see above) containing the information about the geographical location of the exported raster. Previously I copied the header manually in the new matrix (*.dat) but it would save time when it is possible to write the header in the target file.

In the syntax reference I found no information about writing headers within the SAVE TRANSLATE command. Only in the SET command is a information about headers. Is it possible to embed this command in your syntax (see below)?

data list free / a b .
begin data
1 3    2 2    3 4    4 5    5 6    6 7
end data.
matrix.
get x / var a.
save reshape(x,3,2)/ outfile *.
end matrix.
save translate / outfile "C:\temp\blah Blah.dat" / type=tab.

Best regards
Holger





 

[David Marso]

Holger,
Best I can think of is create the header file, generate the data matrix and then use HOST command with COPY to append the two files.
See FM for example of HOST.
DOS copy command would look like this:
COPY "C:\header.dat"+"C:\matrix.dat" "C:\finished.dat"
HTH, David

SPSS-user-Austria wrote

David (and others),

Your syntax is working perfect when I want to export single variables as tab delimited matrix in an outfile.

No I have one additional question:

Is it possible to write a header with a few rows in the outfile which does NOT depict the variable names?
This header is essential for importing the new file in my GIS-software. The outfile (with the header in the first six rows) has to look like this:

ncols         484
nrows         378
xllcorner     19920.980000001
yllcorner     248612.98
cellsize      50
NODATA_value  -9999
-9999 -9999 -9999 -9999 -9999 -9999 -9999
-9999 -9999 -9999 -9999 -9999 -9999 -9999
-9999 -9999 -9999 -9999 -9999 -9999 -9999
-9999 -9999 -9999 -9999 -9999 -9999 -9999

The header consist of six rows (see above) containing the information about the geographical location of the exported raster. Previously I copied the header manually in the new matrix (*.dat) but it would save time when it is possible to write the header in the target file.

In the syntax reference I found no information about writing headers within the SAVE TRANSLATE command. Only in the SET command is a information about headers. Is it possible to embed this command in your syntax (see below)?

data list free / a b .
begin data
1 3    2 2    3 4    4 5    5 6    6 7
end data.
matrix.
get x / var a.
save reshape(x,3,2)/ outfile *.
end matrix.
save translate / outfile "C:\temp\blah Blah.dat" / type=tab.

Best regards
Holger

[Bruce Weaver]

I was thinking along those same lines.  The HOST command (for MS-DOS) would be:

HOST command = "copy C:\header.dat + C:\data.dat  C:\finished.dat".

David Marso wrote

Holger,
Best I can think of is create the header file, generate the data matrix and then use HOST command with COPY to append the two files.
See FM for example of HOST.
DOS copy command would look like this:
COPY "C:\header.dat"+"C:\matrix.dat" "C:\finished.dat"
HTH, David

SPSS-user-Austria wrote

David (and others),

Your syntax is working perfect when I want to export single variables as tab delimited matrix in an outfile.

No I have one additional question:

Is it possible to write a header with a few rows in the outfile which does NOT depict the variable names?
This header is essential for importing the new file in my GIS-software. The outfile (with the header in the first six rows) has to look like this:

ncols         484
nrows         378
xllcorner     19920.980000001
yllcorner     248612.98
cellsize      50
NODATA_value  -9999
-9999 -9999 -9999 -9999 -9999 -9999 -9999
-9999 -9999 -9999 -9999 -9999 -9999 -9999
-9999 -9999 -9999 -9999 -9999 -9999 -9999
-9999 -9999 -9999 -9999 -9999 -9999 -9999

The header consist of six rows (see above) containing the information about the geographical location of the exported raster. Previously I copied the header manually in the new matrix (*.dat) but it would save time when it is possible to write the header in the target file.

In the syntax reference I found no information about writing headers within the SAVE TRANSLATE command. Only in the SET command is a information about headers. Is it possible to embed this command in your syntax (see below)?

data list free / a b .
begin data
1 3    2 2    3 4    4 5    5 6    6 7
end data.
matrix.
get x / var a.
save reshape(x,3,2)/ outfile *.
end matrix.
save translate / outfile "C:\temp\blah Blah.dat" / type=tab.

Best regards
Holger

[Art Kendall]

I have not tried it out much yet but version 20 has mapping and has some capabilities to read/write ARCGIS files.

Maybe you can do your mapping directly ins SPSS.

Even if you are not using ARCGIS many other GIS programs can use files in its formats.

Art Kendall
Social Research Consultants

On 10/7/2011 7:14 AM, SPSS-user-Austria wrote:

David (and others),

Your syntax is working perfect when I want to export single variables as tab
delimited matrix in an outfile.

No I have one additional question:

Is it possible to write a header with a few rows in the outfile which does
NOT depict the variable names?
This header is essential for importing the new file in my GIS-software. The
outfile (with the header in the first six rows) has to look like this:

ncols         484
nrows         378
xllcorner     19920.980000001
yllcorner     248612.98
cellsize      50
NODATA_value  -9999
-9999   -9999   -9999   -9999   -9999   -9999   -9999
-9999   -9999   -9999   -9999   -9999   -9999   -9999
-9999   -9999   -9999   -9999   -9999   -9999   -9999
-9999   -9999   -9999   -9999   -9999   -9999   -9999

The header consist of six rows (see above) containing the information about
the geographical location of the exported raster. Previously I copied the
header manually in the new matrix (*.dat) but it would save time when it is
possible to write the header in the target file.

In the syntax reference I found no information about writing headers within
the SAVE TRANSLATE command. Only in the SET command is a information about
headers. Is it possible to embed this command in your syntax (see below)?

data list free / a b .
begin data
1 3    2 2    3 4    4 5    5 6    6 7
end data.
matrix.
get x / var a.
save reshape(x,3,2)/ outfile *.
end matrix.
save translate / outfile "C:\temp\blah Blah.dat" / type=tab.

Best regards
Holger










--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/export-variable-as-matrix-ASCII-tp4868248p4879719.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

[SPSS-user-Austria]

Thanks for all your answers!


David,
your DOS command is perfect and can directly embedded in the HOST command of the SPSS syntax. Look at this information of the SPSS Support homepage:
SPSS Support HOST command

That means I had to change the directory first and afterwards I had to integrate your DOS command. The solution was:

cd 'c:\temp\'.
HOST command=['COPY "Header.txt" + "export_file.dat" "final_with header.txt"'].


Bruce,
thanks for your suggestions to. But when I tried to execute your command, it does not work because of the missing characters (i.e. the brackets and the single quotes) -> see my solution


Art,
thanks for this information! For easy mapping this is in fact a nice improvement. But for my spatial analysis I have still to use The ArcGIS package..


Cheers,
Holger

[Jon K Peck]

It is not necessary to change the SPSS working directory in order to use HOST.  You can issue a cd command in the list of commands for HOST, or you can just include it in the COPY syntax.

HTH,

Jon Peck (no "h")
Senior Software Engineer, IBM
[hidden email]
new phone: [phone]




From:        SPSS-user-Austria <[hidden email]>
To:        [hidden email]
Date:        10/10/2011 07:57 AM
Subject:        Re: [SPSSX-L] export variable as matrix (ASCII)
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Thanks for all your answers!


David,
your DOS command is perfect and can directly embedded in the HOST command of
the SPSS syntax. Look at this information of the SPSS Support homepage:
https://www-304.ibm.com/support/docview.wss?uid=swg21481481 SPSS Support
HOST command

That means I had to change the directory first and afterwards I had to
integrate your DOS command. The solution was:

cd 'c:\temp\'.
HOST command=['COPY "Header.txt" + "export_file.dat" "final_with
header.txt"'].


Bruce,
thanks for your suggestions to. But when I tried to execute your command, it
does not work because of the missing characters (i.e. the brackets and the
single quotes) -> see my solution


Art,
thanks for this information! For easy mapping this is in fact a nice
improvement. But for my spatial analysis I have still to use The ArcGIS
package..


Cheers,
Holger

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/export-variable-as-matrix-ASCII-tp4868248p4887385.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

[Art Kendall]

For those who have not see it yet IIRC the SPSS mapping actually uses ArcGIS.  SPSS eases the interface.
HealthLandscape is a free resource for moderate size GIS uses.  It has a vast amount of data already gathered and available.  It too eases the interface with Arc GIS.

Art Kendall
Social Research Consultants

On 10/10/2011 4:18 AM, SPSS-user-Austria wrote:

Thanks for all your answers!


David,
your DOS command is perfect and can directly embedded in the HOST command of
the SPSS syntax. Look at this information of the SPSS Support homepage:
https://www-304.ibm.com/support/docview.wss?uid=swg21481481 SPSS Support
HOST command

That means I had to change the directory first and afterwards I had to
integrate your DOS command. The solution was:

cd 'c:\temp\'.
HOST command=['COPY "Header.txt" + "export_file.dat" "final_with
header.txt"'].


Bruce,
thanks for your suggestions to. But when I tried to execute your command, it
does not work because of the missing characters (i.e. the brackets and the
single quotes) -> see my solution


Art,
thanks for this information! For easy mapping this is in fact a nice
improvement. But for my spatial analysis I have still to use The ArcGIS
package..


Cheers,
Holger

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/export-variable-as-matrix-ASCII-tp4868248p4887385.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