楼主: 3qsir
230 2

[程序分享] How do Use Proc SQL to Extract Rolling Column files more efficiency ? [推广有奖]

  • 2关注
  • 1粉丝

副教授

52%

还不是VIP/贵宾

-

威望
0
论坛币
13606 个
通用积分
27.0809
学术水平
0 点
热心指数
0 点
信用等级
0 点
经验
71836 点
帖子
367
精华
0
在线时间
931 小时
注册时间
2011-5-18
最后登录
2021-3-6

楼主
3qsir 发表于 2014-3-2 10:57:19 |AI写论文
30论坛币
How do Use Proc SQL to Extract Rolling Column files more efficiency ?
Hi ! Sir :
  For SAS I am begginger ,Recently I used the tradional method (Proc SQL) to extract Rolling Columns files every 24 columns
from MONTHLY.xlsx file,I find my code is too long and to solw, Can you improve my code which became more efficiency ?
My Object is like the example's JPG file,those files will be involved from H199101 H199102 ... and to H201103.
Thank for your helps !

*Start from  199101*
%_eg_conditional_dropds(WORK.H199101);
PROC SQL;
CREATE TABLE WORK.H199101 AS
SELECT t1.IND,
t1.'199101'n AS K1,
t1.'199102'n AS K2,
t1.'199103'n AS K3,
t1.'199104'n AS K4,
t1.'199105'n AS K5,
t1.'199106'n AS K6,
t1.'199107'n AS K7,
t1.'199108'n AS K8,
t1.'199109'n AS K9,
t1.'199110'n AS K10,
t1.'199111'n AS K11,
t1.'199112'n AS K12,
t1.'199201'n AS K13,
t1.'199202'n AS K14,
t1.'199203'n AS K15,
t1.'199204'n AS K16,
t1.'199205'n AS K17,
t1.'199206'n AS K18,
t1.'199207'n AS K19,
t1.'199208'n AS K20,
t1.'199209'n AS K21,
t1.'199210'n AS K22,
t1.'199211'n AS K23,
t1.'199212'n AS K24
FROM WORK.MONTHLY t1;
QUIT;

*End to 201103*
%_eg_conditional_dropds(WORK.H201103);
PROC SQL;
CREATE TABLE WORK.H201103 AS
SELECT t1.IND,
t1.'201103'n AS K1,
t1.'201104'n AS K2,
t1.'201105'n AS K3,
t1.'201106'n AS K4,
t1.'201107'n AS K5,
t1.'201108'n AS K6,
t1.'201109'n AS K7,
t1.'201110'n AS K8,
t1.'201111'n AS K9,
t1.'201112'n AS K10,
t1.'201201'n AS K11,
t1.'201202'n AS K12,
t1.'201203'n AS K13,
t1.'201204'n AS K14,
t1.'201205'n AS K15,
t1.'201206'n AS K16,
t1.'201207'n AS K17,
t1.'201208'n AS K18,
t1.'201209'n AS K19,
t1.'201210'n AS K20,
t1.'201211'n AS K21,
t1.'201212'n AS K22,
t1.'201301'n AS K23,
t1.'201302'n AS K24
FROM WORK.MONTHLY t1;
QUIT;

关键词:EFFICIENCY proc sql extract rolling column files efficiency Recently involved example

沙发
3qsir 发表于 2014-3-2 11:10:04
Forget paste input and output files

Monthly.xlsx lis ike below:
IND        199101         199102 ...        199103         201301         201302
sz1_bm1        -3.45        8.05        -0.66        5.76        -1.64
sz1_bm2        -1        8.22        -1.68        5.77        -2.35
sz1_bm3        -1.93        8.47        -1.13        5.68        -1.99
sz1_bm4        -1.86        7.7        -2.03        6.87        -1.44
sz1_bm5        -3.15        8.36        -2.42        10.22        -2.61
sz2_bm1        -1.6        9.68        -2.05        4.5        1.2
sz2_bm2        -2.89        7.18        -5.59        5.84        -0.19
sz2_bm3        -1.81        9.61        -4.82        7.69        1.45
sz2_bm4        -0.52        7.69        -3.59        9.59        -0.4
sz2_bm5        -3.12        11.2        -5.58        9.26        -2.07

H201103 outcomes:
IND        K1        K2        K23        K24
sz1_bm1        8.05        -0.66        2.1        2.1
sz1_bm2        8.22        -1.68        2.06        3.67
sz1_bm3        8.47        -1.13        1.86        4.47
sz1_bm4        7.7        -2.03        0.62        4.41
sz1_bm5        8.36        -2.42        -0.42        4.94
sz2_bm1        9.68        -2.05        4.15        4.96
sz2_bm2        7.18        -5.59        3.8        5.79

.
.

H201103 outcomes:
IND        K1        K2        K23        K24
sz1_bm1        0.69        7.47        5.76        -1.64
sz1_bm2        1.35        7.32        5.77        -2.35
sz1_bm3        0.81        7.07        5.68        -1.99
sz1_bm4        2.02        6.44        6.87        -1.44
sz1_bm5        0.8        6.42        10.22        -2.61
sz2_bm1        1.35        7.53        4.5        1.2
sz2_bm2        2.39        6.78        5.84        -0.19

藤椅
3qsir 发表于 2014-3-2 11:27:19
This is correct outcomes:

H199101 outcomes:
IND        K1          K2        K23        K24
sz1_bm2    8.05        -0.66        2.1        2.1
sz1_bm2        8.22        -1.68        2.06        3.67
sz1_bm3        8.47        -1.13        1.86        4.47
sz1_bm4        7.70        -2.03        0.62        4.41
sz1_bm5        8.36        -2.42        -0.42        4.94
sz2_bm1        9.68        -2.05        4.15        4.96
sz2_bm2        7.18        -5.59        3.8        5.79

.
.
H201103 outcomes:
IND        vK1         K2        K23         K24
sz1_bm1        0.69        7.47        5.76        -1.64
sz1_bm2        1.35        7.32        5.77        -2.35
sz1_bm3        0.81        7.07        5.68        -1.99
sz1_bm4        2.02        6.44        6.87        -1.44
sz1_bm5        0.8        6.42        10.22        -2.61
sz2_bm1        1.35        7.53        4.50        1.20
sz2_bm2        2.39        6.78        5.84        -0.19

您需要登录后才可以回帖 登录 | 我要注册

本版微信群
加好友,备注cda
拉您进交流群
GMT+8, 2025-12-9 07:07