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 |
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.
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'
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.