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
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.
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!
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: