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;
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
@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;
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.
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
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.