topic Re: proc sql create table to count records split into three criteria in SAS Programming

proc sql create table to count records split into three criteria

jeremy4 — Thu, 02 May 2019 11:06:38 GMT

Hi,

I have a variable, utilisation, which has a range of values. I have the following SAS code below to start but is there a line(s) that someone could help me with in order to create a table which counts the number of records where the variable utilisation=0, utilisation<0.65 and utilisation>0.65, so the output data looks like the table below and have the same headings? Thanks.

proc sql;

create table utilisation as

select

utilisation,

count(*)

from rc.accounts

group by 1;

quit ;

Re: proc sql create table to count records split into three criteria

PeterClemmensen — Thu, 02 May 2019 11:15:55 GMT

In this scenario, should the zero values also be counted as under .65 or are they mutually exclusive? 🙂

Re: proc sql create table to count records split into three criteria

jeremy4 — Thu, 02 May 2019 11:20:16 GMT

Hi,

Thanks for pointing that out - utilisation=0 should be in one band. Anything greater than 0 and less than 0.65 i.e. 0<utilisation<0.65 should be in band 2, utilisation>0.65 in band 3.

Re: proc sql create table to count records split into three criteria

PeterClemmensen — Thu, 02 May 2019 11:22:00 GMT

Ok. So what about negative values? 🙂

Re: proc sql create table to count records split into three criteria

PeterClemmensen — Thu, 02 May 2019 11:28:29 GMT

I wouldn't use PROC SQL for a problem like this. Use PROC FORMAT and PROC FREQ in combination like this

data somedata;
   do x=1 to 1000;
      utilisation=rand('uniform');
      output;
   end;
   do x=1 to 100;
      utilisation=0;
      output;
   end;
run;

proc format library=work;
   value overunder
      0           = '0'
      0 <- .65    = '<= 0.65'
      .65 <- high = '> 0.65'
      other       = 'Unknown';
run;

proc freq data=somedata;
   tables utilisation / nocum nopercent out=want;
   format utilisation overunder.;
run;

Re: proc sql create table to count records split into three criteria

jeremy4 — Thu, 02 May 2019 11:27:43 GMT

Hi,

I've done a check on the minimum values and there aren't any negative values for the 'utilisation' variable. So the table would count the number of records that fit the following three criteria:

Band 1: Utilisation = 0
Band 2: 0<Utilisation<=0.65
Band 3: Utilisation>0.65

Re: proc sql create table to count records split into three criteria

PeterClemmensen — Thu, 02 May 2019 11:28:51 GMT

Ok. Please see if the below code works for you 🙂

Re: proc sql create table to count records split into three criteria

jeremy4 — Thu, 02 May 2019 11:45:44 GMT

Hi,
Thanks a lot for your code - the results look good but can I double-check that for the part of your code where you write 0 <- .65 = '<= 0.65', do I need to add an = somewhere for the 0 <- .65 (i.e. LHS of that line), so that it matches the criteria that 0<Utilisation<=0.65?

Re: proc sql create table to count records split into three criteria

PeterClemmensen — Thu, 02 May 2019 11:49:17 GMT

Always good to double check. But no, no need to add an equal sign 🙂

This

0 <- .65

does not include 0, but does include 0.65

This

0 - .65

includes both values and this

0 <-< .65

includes neither 🙂