Merging datasets

paues · Postby **paues** » Wed May 27, 2026 4:55 am

I'm trying to do either a one-to-many or a many-to-one merge of two datasets (attached). The program file where I try to do it is below.

Code: Select all

mode quiet
close @all
import(page=key) .\key.txt @freq u 1
%name = @wright(@wlookup("*"), 1)
rename {%name} name
%name = "name"
rename series* key*
%keys = @wlookup("key*")

%objects = @wdelim( _
	@wreplace( _
		"vintage year " + %keys + " " + "value01 value02 value03 value04", _
		"*", _
		"""*""" _
	), _
	" ", _
	"," _
)
pageload(page=data) .\data.txt nonames names=({%objects})

' Attempt 1
pageselect key
pagecopy(page=key_data)
import(type=txt, resize) _
	.\data.txt _
	colhead=0 _
	names=({%objects}) _
	@id {%keys} _
	@destid {%keys}
show key* name vintage year value*

' Attempt 2
pageselect data
pagecopy(page=data_key)
%names = @wdelim( _
	@wreplace( _
		%keys + " " + "name", _
		"*", _
		"""*""" _
	), _
	" ", _
	"," _
)
import _
	.\key.txt _
	colhead=0 _
	names=({%names}) _
	@id {%keys} _
	@destid {%keys}
show key* name vintage year value*

The first attempt (one-to-many) results in a page "key_data" where only the first occurrence of the id series in data.txt is matched and only if the id series doesn't contain any NA values. The second attempt (many-to-one) results in a page "data_key" where only the first occurrence of the id series in data.txt is assigned the name column and only if the id series doesn't contain any NA values.

What am I doing wrong?

(The end goal is to stack the value?? columns and create a page with quarterly frequency.)

paues · Postby **paues** » Wed May 27, 2026 11:06 am

This seems to handle the second case:

Code: Select all

mode quiet
close @all
import(page=key) .\key.txt @freq u 1
%name = @wright(@wlookup("*"), 1)
rename {%name} name
%name = "name"
rename series* key*
%keys = @wlookup("key*")

%objects = @wdelim( _
	@wreplace( _
		"vintage year " + %keys + " " + "value01 value02 value03 value04", _
		"*", _
		"""*""" _
	), _
	" ", _
	"," _
)
pageload(page=data) .\data.txt nonames names=({%objects})

pagecopy(page=data_key)
copy(merge,nacat) key\* data_key\ @src key01 key02 @dest key01 key02
show key* name vintage year value*

EViews Gareth · Postby **EViews Gareth** » Fri May 29, 2026 11:08 pm

Are you still having issues with the first case (or the second)? I took a look, but think I need more information on what you're trying to do. I put a stop right before your "attempt 1", just to load the two pages and do the renames. But after that I can't see what you're trying to accomplish - how you match the series/ids etc.... Could you describe more?

paues · Postby **paues** » Sat May 30, 2026 2:29 am

I think that I have the second case sorted with the code that I posted. But the first case still eludes me.

In the end I want to create a quarterly dated page with a range from 1993Q1 to 1997Q4 with the series name_a, ..., name_i. The quarterly values for each series is in the four last columns of data.txt. The first column in data.txt is irrelevant. The second column is the year. If the third and fourth columns in data.txt match the first and second column in key.txt, then the resulting series should pick its name from the third column of key.txt.

EViews Gareth · Postby **EViews Gareth** » Mon Jun 01, 2026 12:50 am

Here's a stab.

Code: Select all

mode quiet
close @all
import(page=key) .\key.txt @freq u 1
%name = @wright(@wlookup("*"), 1)
rename {%name} name
%name = "name"
rename series* key*
%keys = @wlookup("key*")

%objects = @wdelim( _
	@wreplace( _
		"vintage year " + %keys + " " + "value01 value02 value03 value04", _
		"*", _
		"""*""" _
	), _
	" ", _
	"," _
)
pageload(page=data) .\data.txt nonames names=({%objects})

'attempt0
d vintage
copy(nacat) key\name data\name @src key01 key02 @dest key01 key02
pagestack value? 
series date = @dateval(@str(year)+"Q"+var01)
d id01 key var01 year
pageunstack name date
d value name
rename value* *

EViews.com

Merging datasets

Merging datasets

Re: Merging datasets

Re: Merging datasets

Re: Merging datasets

Re: Merging datasets

Who is online