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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.