sas - 在 SAS 中将两个 Tables 连接在一起

我正在处理这个 SAS 代码,需要帮助才能加入下面的两个 tables。尝试加入两个 tables 时出现错误。

要求:一。左连接 Table B 到 Table A

Table 答:

PROC SQL; 
 create table stand as select distinct
    put(datepart(Max(a.REPORT_DATE)),Date9.) as M_Date
  , a.BUSINESS_GROUP as PORTF_LEVEL1
  , A.SPLIT as PORTF_LEv2
  , Count(distinct a.Report_Date) as Number_of_Days 
  , (B.TOTAL_BREACH/Count(distinct a.Report_Date))*100 as FREQ 
  , A.MINIMUM_ACCEPTABLE_COUNT
  , A.MAX_COUNT 
  , (case WHEN (B.TOTAL_BREACH/Count(distinct a.Report_Date)) * 100 LT MIN_COUNT 
          THEN 'TRUE' ELSE 'FALSE' END) as NUMBER__UNDER
  , (case WHEN (B.TOTAL_BREACH/Count(distinct a.Report_Date)) * 100 GT MAX_COUNT THEN 'TRUE' ELSE 'FALSE' END) as NUMBER__OVER
 from temp a 
   INNER join 
   ( select BUSINESS_GROUP as PORTF_LEVEL1
           ,SPLIT AS PORTF_LEv2
           ,Count(distinct c.Report_Date) as Number_of_Days 
      from temp c 
        Inner join temp2 d 
      on c.Report_Date=d.Report_Date 
      WHERE &Alert and TENOR = '+'
        and datepart(c.REPORT_DATE) ge '31-APR-21'd
        and datepart(c.REPORT_DATE) le '31-APR-22'd
      Group by BUSINESS_GROUP, SPLIT
   )B 
  on a.BUSINESS_GROUP = b.PORTF_LEVEL1
  AND a.SPLIT = b.PORTF_LEVEL2 

 INNER JOIN temp2 e 
 on a.REPORT_DATE = e.REPORT_DATE 
 where &Alert and TENOR = '+'
   and datepart(a.REPORT_DATE) ge '31-APR-21'd
   and datepart(a.REPORT_DATE) le '31-APR-22'd 
 Group by Business_GROUP, SPLIT 
; 
QUIT;

Table 乙:

在 table B 中,我试图找到变量 Data_M 的中值。代码似乎没问题。我只需要帮助将上面的 Table B 连接到 table A。

Proc sql outobs=1; create table median_dt1 as select distinct put(datepart(max(REPORT_DATE)), date9.) as M_Date , median(Data_M) as median_data from transp
WHERE datepart(REPORT_DATE) ge '01-APR-22'd and datepart(REPORT_DATE) le '31-APR-22'd group by BUSINESS_GROUP order by Report_Date Desc; quit;

先感谢您!

sas

回答1

from temp a 
   INNER join 
   ( select BUSINESS_GROUP as PORTF_LEVEL1
           ,SPLIT AS PORTF_LEv2
           ,Count(distinct c.Report_Date) as Number_of_Days 
      from temp c 
        Inner join temp2 d 
      on c.Report_Date=d.Report_Date 
      WHERE &Alert and TENOR = '+'
        and datepart(c.REPORT_DATE) ge '31-APR-21'd
        and datepart(c.REPORT_DATE) le '31-APR-22'd
      Group by BUSINESS_GROUP, SPLIT
   )B 
  on a.BUSINESS_GROUP = b.PORTF_LEVEL1
  AND a.SPLIT = b.PORTF_LEVEL2

您正在尝试加入 b.PORTF_LEVEL2。但是,B 中不存在该列。但存在“PORTF_LEV2”列。试试看?

如果这不能解决问题,请粘贴您收到的完整错误消息。

相似文章

sas - 使用不同的参数并行运行相同的 SAS 程序

我目前正在优化SASEG中的脚本。该脚本基本上必须从2012年以来的每个月每天运行一次。直到2022年所有时期的代码运行时间不到24小时,但时不时地超过这个阈值。该过程(这是一个宏)的结构如下:从多个...