Blank '.' Data I need to Group

Reply
Frequent Contributor
Posts: 138

Blank '.' Data I need to Group

Hi,

I have this code below and when joining to another table some accounts dont have a score and therefore the output populates with a '.'  How can I group up the accounts with blank information, do i need to do this to my code I run off the bak of this, below the dataset code?

DATASET

proc sql;

create table work.dcm as

select

month_date,

accountno as accnum,

mean(score_DSCORDELPHI) as avg_delphi,

case

when DSCORDELPHI < 500 then '< 500'

when DSCORDELPHI between 500 and 599 then '500-599'

when DSCORDELPHI between 600 and 699 then '600-699'

when DSCORDELPHI between 700 and 799 then '700-799'

when DSCORDELPHI between 800 and 899 then '800-899'

when DSCORDELPHI between 900 and 1100 then '900-1100'

when DSCORDELPHI > 1100 then '> 1100'

else 'null' end as Delphi

from gdcm.dcm201409;

quit;

CODE

proc sql;

create table work.rwa_dcm as

select

distinct month_date,

SUM(rwa_prov) as rwa,

delphi

from

work.complete_dataset

group by

month_date, delphi;

quit;

MY OUTPUT -

rwaDelphiavg_delphi
148412508.8500-599743.237
156185401.5 .
350690603.4< 500743.237
368501967.6900-1100743.237
524554601.5600-699743.237
1194299549700-799743.237
1635315502800-899743.237
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Blank '.' Data I need to Group

Sorry, by what do you mean "group blank records"?  You could just proc sort the data by Delphi avg_Delphi, then missings will be at the top.

Frequent Contributor
Posts: 138

Re: Blank '.' Data I need to Group

Sorry,

I have these groupings already below but I have an extra one of which is the full stop '.'. How can I gorup the '.' to <500?

when DSCORDELPHI < 500 then '< 500'

when DSCORDELPHI between 500 and 599 then '500-599'

when DSCORDELPHI between 600 and 699 then '600-699'

when DSCORDELPHI between 700 and 799 then '700-799'

when DSCORDELPHI between 800 and 899 then '800-899'

when DSCORDELPHI between 900 and 1100 then '900-1100'

when DSCORDELPHI > 1100 then '> 1100'

Valued Guide
Posts: 3,206

Re: Blank '.' Data I need to Group

That . is the missing. You are caught by the difference meanings in SQL null and SAS missing.

In normal SQL the null value is excluded (3 value logic unknown aside false/true)  With SQL on sas-datasets missings are included. Processing data with SAS procs missings are excluded.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 3 replies
  • 178 views
  • 0 likes
  • 3 in conversation