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

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

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

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.

1. %macro m(v);
   
2. %let y=%substr(&v.,1,4);
   
3. %let m=%substr(&v.,5,2);
   
4. %do i=1 %to 24;
   
5.     %let mf=%sysfunc(putn(%eval(&m.+&i.-1),z2.));
   
6.     %if &mf.>24 %then %do;
   
7.         %let mf=%sysfunc(putn(%eval(&mf.-24),z2.));
   
8.         %let yf=%eval(&y.+2);
   
9.     %end;
   
10.     %else %if &mf.>12 %then %do;
    
11.         %let mf=%sysfunc(putn(%eval(&mf.-12),z2.));
    
12.         %let yf=%eval(&y.+1);
    
13.     %end;
    
14.     %else %let yf=&y.;
    
15. 
    
16.     ,t1."&yf.&mf."n as K&i.;
    
17. 
    
18. %end;
    
19. %mend m;
    
20. %m(201103);

复制代码

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

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".

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)

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

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.

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

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;