PROC SQL join question

[Maisha_Huq]

purpose: create a table like the following

Variable	marketing	nomarketing	difference	Probt
abc	.(           .)	.(           .)	.	.
cde	8.4(        27.8)	8.4(        27.8)	0	0.9886
efg	.(           .)	.(           .)	.	0.0236
hij	.(           .)	.(           .)	.	0.8113
klm	.(           .)	.(           .)	.	0.1963
nop	.(           .)	.(           .)	.	0.0567
if	0.3(         5.4)	0.2(         4.6)	0.1	0.157
region01	5.3(        22.4)	5.7(        23.3)	-0.4	.
region02	7.9(          27)	8.1(        27.2)	-0.2	.
region03	8.8(        28.3)	8.5(        27.9)	0.3	.
region04	17.2(        37.8)	17.1(        37.6)	0.1	.
region05	25.9(        43.8)	25.5(        43.6)	0.4	.
region06	10.2(        30.2)	10.1(        30.1)	0.1	.
region07	4.8(        21.4)	4.7(        21.1)	0.1	.
region08	2.8(        16.5)	3.1(        17.3)	-0.3	.
region09	14.5(        35.2)	14.7(        35.5)	-0.2	.
region10	2.6(        15.9)	2.6(        15.8)	0	.

(some) syntax:

proc sql;

create table table2_amp as

select  mean_amp.variable,      

        amp.marketing,

        amp.nomarketing,

        amp.difference,

        ttest.Probt  

from    mean_amp as amp left join  ttest_amp as ttest on amp.variable= ttest.variable;

quit;

issue:

As seen above, it looks like the way the input data sets were joined in PROC SQL needs to be revised because many values did not get copied from the input data sets.  In other words, the input data sets' "variable" variable don't have observations in the same order and it looks to me like PROC SQL requires them to be when joining tables.  How could I either sort the input datasets' variable valid values in the order I want or revise the SQL step?

Thank you so much

[RW9]

Well, firstly your example code is invalid.  You use an alias for dataset mean_amp called amp, in you select you use an alias mean_amp which does not exist.

Secondly, what do these variables look like in the datasets:

from    mean_amp as amp left join  ttest_amp as ttest on amp.variable= ttest.variable;

AMP.VARIABLE and TTEST.VARIABLE.

It is irrelevant what the sort order is, SQL does not require presorted data, so there must be some disconnect between the data contained in each.

[Maisha_Huq]

The amp.variable valid values (in the following order):

brc

brf

es

fed

fns

ic

if

osharegion01

osharegion02

osharegion03

osharegion04

osharegion05

osharegion06

osharegion07

osharegion08

osharegion09

osharegion10

The ttest.variable valid values:

fed
fns
es
brf
brc
if
ic

osharegion01

osharegion02

osharegion03

osharegion04

osharegion05

osharegion06

osharegion07

osharegion08

osharegion09

osharegion10

Does that help? Thanks, RW9!

[RW9]

Not really I am afraid.  From that there is no reason why the join would not work.  Perhaps provide, in a datastep, sample data from both datasets.  I note that the values you post do not match the ones in the output provided in the first post.  In your first post it would seem to my mind that dataset TTEST only have variable=CDE to IF, whereas the other one has all of them.