How do Use Proc SQL to Extract Rolling Column files more efficiency ?

3qsir

2974

收藏 2014-03-02

悬赏 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 !
----------------------------------------------------------------------------------------------------------------------------------------------
SAS Code:
*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;
----------------------------------------------------------------------------------------------------------------------------------------------
Monthly.xlsx
IND       199101  199102    201301 201302
sz1_bm1  -3.45    8.05       5.76       -1.64
sz1_bm2  -1          8.22       5.77          -2.35
sz1_bm3 -1.93       8.47       5.68       -1.99
sz1_bm4 -1.86       7.7       6.87       -1.44
sz1_bm5 -3.15       8.36       10.22    -2.61
sz2_bm1 -1.6          9.68          4.5          1.2
sz2_bm2 -2.89       7.18       5.84    -0.19
-------------------------------------------------------------------------------------------------------------------------------------------------
H199101 SAS outcomes:
IND          K1 K2    K23 K24
sz1_bm1 -3.45 8.05 -0.66  2.1
sz1_bm2  -1 8.22 -1.68  2.06
sz1_bm3 -1.93 8.47  -1.13  1.86
sz1_bm4 -1.86 7.7  -2.03 0.62
sz1_bm5 -3.15 8.36  -2.42 -0.42
sz2_bm1 -1.6  9.68  4.15 4.96
sz2_bm2 -2.89 7.18 3.8 5.79
.
.
H201103 SASoutcomes:
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

最佳答案

nomad5 查看完整内容

这个宏只是为了循环楼主程序里面的select步，简化程序用的，并不能解决楼主的整个问题。还有一个问题是楼主select中使用的是单引号，但是我最终出现的是双引号，也许会对整个程序产生影响。

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

全部回复

nomad5

2014-3-2 13:31:46

这个宏只是为了循环楼主程序里面的select步，简化程序用的，并不能解决楼主的整个问题。
还有一个问题是楼主select中使用的是单引号，但是我最终出现的是双引号，也许会对整个程序产生影响。

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

nomad5

2014-3-4 14:33:39

I don't know why you use PROC SQL, and I don't see the raw data.
I only use a macro to instead of your code.

复制代码

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

3qsir

2014-3-4 23:21:56

Thanks nomad5.
It is so difficulty for me , I understand your code only focus on H201103 sas outcome
我想输出结果是包含(CREATE TABLE WORK)H199101, H199102 ... and to H201103
According your macro (m) : I must construct a loop from 199101 to 201103
Do i=199101 to 201103;
%m(i)
end;
I do not consider i is 非连续(monthly time series) after the loop 13 times

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

3qsir

2014-3-5 22:41:56

I used my original code run is OK,
But run  the nomad5's Macro code appear Error .
-----------------------------------------------------------
*/my original code/*
%_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;
---------------------------------------------------------------------------------------
*\The nomad5's Macro code\*
15       %macro m(v);
16       %let y=%substr(&v.,1,4);
17       %let m=%substr(&v.,5,2);
18       %do i=1 %to 24;
19             %let mf=%sysfunc(putn(%eval(&m.+&i.-1),z2.));
20             %if &mf.>24 %then %do;
21                %let mf=%sysfunc(putn(%eval(&mf.-24),z2.));
22                %let yf=%eval(&y.+2);
23             %end;
24             %else %if &mf.>12 %then %do;
25                %let mf=%sysfunc(putn(%eval(&mf.-12),z2.));
26                %let yf=%eval(&y.+1);
27             %end;
28             %else %let yf=&y.;
29             ,t1."&yf.&mf."n as K&i.;
30       %end;
31       %mend m;
32       %m(201103);
NOTE: Line generated by the invoked macro "M".
32       ,t1."&yf.&mf."n as K&i.;
ERROR 180-322: Statement is not valid or it is used out of proper order.
NOTE: Line generated by the invoked macro "M".

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

3qsir

2014-3-6 19:10:01

I Run final sas with macro outcome only one column "IND"
----------------------------------------------------------------------
ERROR: Column '&yf&mf.'n could not be found in the table/view identified with the
correlation name T1.
-------------------------------------------------------------------------
%macro m(v);
PROC SQL;
CREATE TABLE WORK.H&v AS
SELECT t1.IND
FROM WORK.MONTHLY t1;
%let y=%substr(&v.,1,4);
%let m=%substr(&v.,5,2);
%do i=1 %to 24;
   %let mf=%sysfunc(putn(%eval(&m.+&i.-1),z2.));
   %if &mf.>24 %then %do;
      %let mf=%sysfunc(putn(%eval(&mf.-24),z2.));
      %let yf=%eval(&y.+2);
   %end;
   %else %if &mf.>12 %then %do;
      %let mf=%sysfunc(putn(%eval(&mf.-12),z2.));
      %let yf=%eval(&y.+1);
   %end;
   %else %let yf=&y.;
SELECT t1.'&yf.&mf.'n as K&i
FROM WORK.MONTHLY t1;
%end;
QUIT;
%mend m;
%m(201103)

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

点击查看更多内容…

intheangel

2014-3-9 09:52:15

你的table 名到低叫啥啊？t1还是monthly t1 不可能有monthly t1这个table的吧

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

3qsir

2014-3-9 12:24:04

I delete All "t1" string , but still error in the code:
--------------------------------------------------------------
15       %macro m(v);
16       PROC SQL;
17       CREATE TABLE WORK.H&v AS
18       SELECT IND
19       FROM WORK.MONTHLY;
20       %let y=%substr(&v.,1,4);
21       %let m=%substr(&v.,5,2);
22       %do i=1 %to 24;
23             %let mf=%sysfunc(putn(%eval(&m.+&i.-1),z2.));
24             %if &mf.>24 %then %do;
25                %let mf=%sysfunc(putn(%eval(&mf.-24),z2.));
26                %let yf=%eval(&y.+2);
27             %end;
28             %else %if &mf.>12 %then %do;
29                %let mf=%sysfunc(putn(%eval(&mf.-12),z2.));
30                %let yf=%eval(&y.+1);
31             %end;
32             %else %let yf=&y.;
33       SELECT '&yf&mf.'n as K&i
34       FROM WORK.MONTHLY;
35       %end;
36          QUIT;
37       %mend m;
38       %m(201103)
ERROR: The following columns were not found in the contributing tables: '&yf&mf.'n.

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

intheangel

2014-3-9 21:37:58

PROC SQL;
CREATE TABLE WORK.H AS
SELECT
%macro m(v=201103);
%let y=%substr(&v.,1,4);
%let m=%substr(&v.,5,2);
%do i=1 %to 24;
   %let mf=%sysfunc(putn(%eval(&m.+&i.-1),z2.));
   %if &mf.>24 %then %do;
      %let mf=%sysfunc(putn(%eval(&mf.-24),z2.));
      %let yf=%eval(&y.+2);
   %end;
   %else %if &mf.>12 %then %do;
      %let mf=%sysfunc(putn(%eval(&mf.-12),z2.));
      %let yf=%eval(&y.+1);
   %end;
   %else %let yf=&y.;
t1.'&yf.&mf.'n as K&i.,
%end;
%mend m;
%m;
t1.ind
FROM WORK.MONTHLY;
quit;

是这样么？这个宏我也看不懂，so marvelous

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

3qsir

2014-3-9 23:32:38

NO!  let us Debug !
---------------------------
15       PROC SQL;
16       CREATE TABLE WORK.H AS
17       SELECT
18       %macro m(v=201103);
19       %let y=%substr(&v.,1,4);
20       %let m=%substr(&v.,5,2);
21       %do i=1 %to 24;
22             %let mf=%sysfunc(putn(%eval(&m.+&i.-1),z2.));
23             %if &mf.>24 %then %do;
24                %let mf=%sysfunc(putn(%eval(&mf.-24),z2.));
25                %let yf=%eval(&y.+2);
26             %end;
27             %else %if &mf.>12 %then %do;
28                %let mf=%sysfunc(putn(%eval(&mf.-12),z2.));
29                %let yf=%eval(&y.+1);
30             %end;
31             %else %let yf=&y.;
32       t1.'&yf.&mf.'n as K&i.,
33       %end;
34       %mend m;
35       %m;
         22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
a numeric constant, a datetime constant, a missing value, (, *, +, -, BTRIM,
            CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SUBSTRING, TRANSLATE, USER, ^,
            ~.
         _
         76
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
36       t1.ind
         __
         180
ERROR 180-322: Statement is not valid or it is used out of proper order.

37       FROM WORK.MONTHLY;
38       quit;

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

yongyitian

2014-3-10 10:38:10

3qsir 发表于 2014-3-9 12:24
I delete All "t1" string , but still error in the code:
------------------------------------------- ...

ERROR: The following columns were not found in the contributing tables: '&yf&mf.'n.

Sas considered '&yf&mf.'n as a column (variable) name, not the expected '201103'n, '201104'n, ...
Reason: Macro variable reference can not be resolved inside single quotation mark.

Try the following code, instead of creating sql code using macro statements.

复制代码

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

nomad5

2014-3-10 18:50:01

复制代码

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

nomad5

2014-3-10 18:54:06

intheangel 发表于 2014-3-9 09:52
你的table 名到低叫啥啊？t1还是monthly t1 不可能有monthly t1这个table的吧

t1的意思是对monthly进行一个缩写的命名。对于这个程序来说其实没用。如果在SQL中存在两个数据集，例如monthly1 and monthly2，那么我们在select里面需要写monthly1.ind and monthly2.ind, 如果使用的缩写，那么就是t1.ind and t2.ind。简化程序用的。

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

3qsir

2014-3-10 20:50:55

nomad5 发表于 2014-3-4 14:33
I don't know why you use PROC SQL, and I don't see the raw data.
I only use a macro to instead of y ...

親愛nomad5楼主 : 谢谢您详细的指导
You matbe forget to rename the old variable "201103..." ------->the new variable "K1 K2...K24" like below:
H201103 SASoutcomes:
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
--------------------------------------------
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

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

3qsir

2014-3-10 21:11:18

NO! ,NO! code not run
I try many times ,even no log file and break , Dialuage boxa ppear "[ERROR]Failed to transcode data from utf-16le to ms-950 encoding because it contained characters which are not suppirted bby you sAS session ecoding...."
/*这个宏只考虑了从输入的&v.之后连续性的24个月，另外你在调用的时候出错了。整体的思路是对年和月进行循环。12月的时候年加1。其实是有函数可以实现的。*/

/*另外为什么一定要用SQL呢，DATA步应该有更简单的过程*/

%macro m(v);

%let y=%substr(&v.,1,4);

%let m=%substr(&v.,5,2);

%do i=1 %to 24;

%let mf=%sysfunc(putn(%eval(&m.+&i.-1),z2.));

%if &mf.>24 %then %do;

      %let mf=%sysfunc(putn(%eval(&mf.-24),z2.));

      %let yf=%eval(&y.+2);

%end;

%else %if &mf.>12 %then %do;

      %let mf=%sysfunc(putn(%eval(&mf.-12),z2.));

      %let yf=%eval(&y.+1);

%end;

%else %let yf=&y.;

,t1."&yf.&mf."n as K&i.

/*这里原来多一个分号，是我写错了*/

%end;

%mend m;

/*%m(201103);*/

proc sql noprint;

create table test as
select t1.IND
/*这个t1.ind是需要加的，因为我在宏里面输出的是 ,t1~ 如果不加这个会出错，因为SQL的逻辑是两个变量之间用逗号分隔，第一个变量前不加逗号。宏里面可以处理但是我嫌麻烦没写*/

%m(201103)

from work.monthly t1

;

quit;

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

3qsir

2014-3-10 21:28:06

The code also Error (No select and cancel "; "at line 51)
See line 51 : ERROR message
----------------------------------------------
14       GOPTIONS ACCESSIBLE;
15       %macro m(v);
16
17       %let y=%substr(&v.,1,4);
18
19       %let m=%substr(&v.,5,2);
20
21       %do i=1 %to 24;
22
23          %let mf=%sysfunc(putn(%eval(&m.+&i.-1),z2.));
24
25          %if &mf.>24 %then %do;
26
27                %let mf=%sysfunc(putn(%eval(&mf.-24),z2.));
28
29                %let yf=%eval(&y.+2);
30
31          %end;
32
33          %else %if &mf.>12 %then %do;
34
35                %let mf=%sysfunc(putn(%eval(&mf.-12),z2.));
36
37                %let yf=%eval(&y.+1);
38
39          %end;
40
41          %else %let yf=&y.;
42
43
44          ,t1."&yf.&mf."n as K&i.
45
46
47       %end;
48
49       %mend m;
50
51       %m(201103);
NOTE: Line generated by the invoked macro "M".
51          ,t1."&yf.&mf."n as K&i.
            _
            180
ERROR 180-322: Statement is not valid or it is used out of proper order.
52
53       GOPTIONS NOACCESSIBLE;
54       %LET _CLIENTTASKLABEL=;
55       %LET _CLIENTPROJECTPATH=;
56       %LET _CLIENTPROJECTNAME=;
57       %LET _SASPROGRAMFILE=;
58
59       ;*';*";*/;quit;run;
60       ODS _ALL_ CLOSE;
61
62
63       QUIT; RUN;
-------------------------------------------

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

3qsir

2014-3-13 18:53:55

亲爱的楼主：因为题目的解答,转贴又臭又长我将您的最后一版(Final SAS code)重发一份问帖起问...因为您给我最后一版的code还是有ERROR先将他close掉 THANKS !

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

最佳答案

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

分享

扫码加好友，拉您进群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群