BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
asimraja
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
alex_a
Fluorite | Level 6

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.

View solution in original post

2 REPLIES 2
alex_a
Fluorite | Level 6

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.

asimraja
Fluorite | Level 6
Thank you alex_a!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 760 views
  • 0 likes
  • 2 in conversation