topic Re: Blank '.' Data I need to Group in SAS Programming

Blank '.' Data I need to Group

anonymous_user — Tue, 21 Oct 2014 10:26:07 GMT

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 -

rwa	Delphi	avg_delphi
148412508.8	500-599	743.237
156185401.5		.
350690603.4	< 500	743.237
368501967.6	900-1100	743.237
524554601.5	600-699	743.237
1194299549	700-799	743.237
1635315502	800-899	743.237

Re: Blank '.' Data I need to Group

RW9 — Tue, 21 Oct 2014 10:44:30 GMT

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.

Re: Blank '.' Data I need to Group

anonymous_user — Tue, 21 Oct 2014 10:49:28 GMT

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'

Re: Blank '.' Data I need to Group

jakarman — Tue, 21 Oct 2014 12:02:32 GMT

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.