Creating varibles from a csv file

Posted by Brian Cooper on Oct 12, 2013; 8:24am
URL: http://spssx-discussion.165.s1.nabble.com/Creating-varibles-from-a-csv-file-tp5722520.html

I need to create the following file structure. The information originally came from an excel pivot table. I have managed to create a csv file with labels in the first column and counts in the second. The desired output is shown below.

 

 

SLA	Country	Gender	Visa Class	Age	Ethnicity	Persons
Acacia Ridge	AFGHANISTAN	Female	866	18-64	Hazaragi	3
		Male	866	18-64	Dari	1
					Hazaragi	6

 

SLA	Country	Gender	Visa Class	Age	Ethnicity	Persons
Acacia Ridge	AFGHANISTAN	Female	866	18-64	Hazaragi	3
Acacia Ridge	AFGHANISTAN	Male	866	18-64	Dari	1
Acacia Ridge	AFGHANISTAN	Male	866	18-64	Hazaragi	6

 

The source data is as below

 

Acacia Ridge	131
AFGHANISTAN	10
Female	3
866	3
18-64	3
Hazaragi	3
Male	7
866	7
18-64	7
Dari	1
Hazaragi	6
AUSTRALIA	5
Female	2
187	1
00-17	1
Not stated	1
485	1
00-17	1
Not stated	1
Male	3
186	1
00-17	1
Not stated	1
820	1
00-17	1
Not stated	1
885	1
00-17	1
Not stated	1

 

I need to create a loop statement which uses the locality as a counter, the number opposite is the total for the locality, the number against the country is the total for the country and the same applied for sex, age and visa class.  Where the number is opposite a language such as Dari it is that number that need to be in the persons column. I have 13 years of data with each data set having approximately 400,000 cases.

 

Is anybody able to assist.

 

Brian cooper