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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.