Question about PROC SQL - Getting 0 on ratio

[Reeza]

SYBASE does truncate when doing division with INT types, so the calculation is giving you 0. 

You can use CAST to do conversion types.

proc sql noprint;
connect to sybaseiq(host='pmba-appdb.bmogc.net' server='PROD_MBA' database='prod_mba' &password.);
create table Engagement_rates ( compress=yes reuse=yes) as select * from connection to sybaseiq
(select	ym_id,
		cast(count(distinct case when selected = 'SELECTED' then USER_ID end) as numeric(15,2))
		/cast(count(distinct case when PRESENTED = 'PRESENTED' then USER_ID end) as numeric(15,2) as Engagement_RT	
     from sa_dcgroup.PERSONETICS_RPT5 as a 
	 group by ym_id
	 order by ym_id
);
quit;

https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/...

[qd96xuweifeng1]

Thanks a lot @Reeza , I tested and the cast function works well. It provides me the correct ratio. There is only minor question not resolved that I cannot apply "count" on single field for more than one times. I will try to ask this in database forum. 

My problem is resolved. Thanks a lot for your help and have a great weekend!

Wayne

[Reeza]

---

@qd96xuweifeng1 wrote:

Hello,

I have one question one Proc SQL, could please anyone advise on it?

My code is very simple as below:

proc sql ;
create table Engagement_rates ( compress=yes reuse=yes) as
select distinct ym_id,
count(distinct case when selected = 'SELECTED' then USER_ID end) as selected,
count(distinct case when PRESENTED = 'PRESENTED' then USER_ID end) as PRESENTED,
count(distinct case when selected = 'SELECTED' then USER_ID end)/
count(distinct case when PRESENTED = 'PRESENTED' then USER_ID end)
as Engagement_RT 
from PERSONETICS_RPT5 as a
group by ym_id
order by ym_id
;quit;

Here, the two green columns work well, but I always get 0 on the third column (Red Part).

So, could anyone please help me to check it?

Any advice will be appreciated!

Thanks,

Wayne

---

For confirmation can you please show a screenshot and log from the following:

proc sql ;
create table Engagement_rates ( compress=yes reuse=yes) as
select distinct ym_id,
count(distinct case when selected = 'SELECTED' then USER_ID end) as selected,
count(distinct case when PRESENTED = 'PRESENTED' then USER_ID end) as PRESENTED,
count(distinct case when selected = 'SELECTED' then USER_ID end)/
count(distinct case when PRESENTED = 'PRESENTED' then USER_ID end)
as Engagement_RT format=8.4
from PERSONETICS_RPT5 as a
group by ym_id
order by ym_id
;quit;

proc print data=engagement_rates(obs=10);
run;

[qd96xuweifeng1]

Hi @Reeza ,

Please find the screenshots below:

Thanks a lot for your time!

Wayne

[Reeza]

Although it shouldn't be a factor, I would also consider dropping the COMPRESS/REUSE options to test this as well, if @Tom solution doesn't fix the issue.

[qd96xuweifeng1]

Hi @Reeza ,

After removing compress and reuse option, the values are still 0. However, if I run additional data step as below, the correct ratio can be obtained

data Engagement_rates ;
set Engagement_rates ;
Engagement_RT = selected/PRESENTED;
run;

Thanks a lot,

Wayne