BookmarkSubscribeRSS Feed

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
3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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'

jakarman
Barite | Level 11

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 --<-----

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1127 views
  • 0 likes
  • 3 in conversation