Hi,
I have a dataset, aggr_tmp_1, which has aggr and aggr_raw as "Character". However, when, using the aggr_tmp_1, proc sql creates a new dataset, aggr_tmp_2, both aggr and aggr_raw are "Numeric".
Can anyone provide insight on why this is happening?
Thank you for your help!
Code:
data aggr_tmp_1;
set aggr_tmp_1;
by AGGR_INSTRUMENT_ID;
length attribute $25.;
attribute = "&var_name.";
retain diff;
if first.AGGR_INSTRUMENT_ID then
diff = 0;
if (not missing(aggr)) and (aggr ne '0') and (aggr ne aggr_raw) then
diff = 1;
run;
proc sql noprint;
create table aggr_tmp_2 as
select aggr_instrument_id,
aggr,
count(aggr_raw) as aggr_raw,
avg (diff) as diff
from aggr_tmp_1
group by aggr_instrument_id, aggr;
quit;
You reinitialize aggr_raw with the value of count(aggr_raw) => Numeric result. Try this:
proc sql noprint;
create table aggr_tmp_2 as
select aggr_instrument_id,
aggr, aggr_raw,
count(aggr_raw) as count_aggr_raw,
avg (diff) as diff
from aggr_tmp_1
group by aggr_instrument_id, aggr;
quit;
Regarding aggr, make sure it exists. By default, SAS converts uninitialized variables in numerical vars.
Edit: It does not make too much sense to keep aggr_raw in the query (depending on your needs). So you better exclude it.
You reinitialize aggr_raw with the value of count(aggr_raw) => Numeric result. Try this:
proc sql noprint;
create table aggr_tmp_2 as
select aggr_instrument_id,
aggr, aggr_raw,
count(aggr_raw) as count_aggr_raw,
avg (diff) as diff
from aggr_tmp_1
group by aggr_instrument_id, aggr;
quit;
Regarding aggr, make sure it exists. By default, SAS converts uninitialized variables in numerical vars.
Edit: It does not make too much sense to keep aggr_raw in the query (depending on your needs). So you better exclude it.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.