SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Percentage out of two query results

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Percentage out of two query results

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;


Accepted Solutions
Solution
‎11-04-2014 10:46 AM
Super User
Super User
Posts: 7,407

Re: Percentage out of two query results

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;

View solution in original post


All Replies
Solution
‎11-04-2014 10:46 AM
Super User
Super User
Posts: 7,407

Re: Percentage out of two query results

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;

Contributor
Posts: 40

Re: Percentage out of two query results

Perfect. I appreciate it.

I need to learn complex joins like this.

Super Contributor
Posts: 578

Re: Percentage out of two query results

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 308 views
  • 0 likes
  • 3 in conversation