Hi,
Can anyone help me to find a way to find the percentage out of two query results
I've below two queries. But I'm trying to find the Result_2ndQuery/Result_1st Query
PROC SQL;
SELECT COUNT(DISTINCT FILEID) FROM MTG.LOGFILE_WIS_FINAL_UI5_FINAL;
SELECT COUNT(DISTINCT FILEID) FROM MTG.LOGFILE_WIS_FINAL_UI5_FINAL WHERE ACTION_NICE = 'Open New Account' AND ACTION_COUNT >1;
Something like:
proc sql;
create table WANT as
select COUNT(DISTINCT A.FILEID) as A_RESULT,
COUNT(DISTINCT B.FILEID) as B_RESULT,
CALCULATED A_RESULT / CALCULATED B_RESULT * 100 as PERCENT
from MTG.LOGFILE_WIS_FINAL_UI5_FINAL A
full join (select * from MTG.LOGFILE_WIS_FINAL_UI5_FINAL WHERE ACTION_NICE = 'Open New Account' AND ACTION_COUNT >1)
on /* not sure from your example what the linking items would be? */;
quit;
Something like:
proc sql;
create table WANT as
select COUNT(DISTINCT A.FILEID) as A_RESULT,
COUNT(DISTINCT B.FILEID) as B_RESULT,
CALCULATED A_RESULT / CALCULATED B_RESULT * 100 as PERCENT
from MTG.LOGFILE_WIS_FINAL_UI5_FINAL A
full join (select * from MTG.LOGFILE_WIS_FINAL_UI5_FINAL WHERE ACTION_NICE = 'Open New Account' AND ACTION_COUNT >1)
on /* not sure from your example what the linking items would be? */;
quit;
Perfect. I appreciate it.
I need to learn complex joins like this.
proc sql;
select
count(distinct case when ACTION_NICE = 'Open New Account' AND ACTION_COUNT >1 then fileid end) / count(distinct fileid) as Pct
from
MTG.LOGFILE_WIS_FINAL_UI5_FINAL;
quit;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.