1.2.1 SAS之禅

简于理解，精于效率。

1.2.2 SAS教程

  • 第1课 SAS基础入门
  • 第2课 SAS拼表
  • 第3课 SQL应用
  • 第4课 PROC步
  • 第5课 SAS进阶
  • 第6课 SAS数据清洗
  • 第7课 SAS宏
    教程为2019.03课程原稿，相对入门，可用以温故知新。

以下摘要之：

1.2.2.1 SAS基础

  1. OPTIONS COMPRESS = YES;
  2. /*    libname ana "";*/
  4. DATA A;
  5. SET SASHELP.CARS;
  6. RUN;
  8. DATA A;
  9. SET SASHELP.CARS;
  10. MSRP1 = MSRP + 1;
  11. RUN;
  13. DATA A;
  14. KEEP MSRP1;
  15. SET SASHELP.CARS;
  16. MSRP1 = MSRP + 1;
  17. RUN;
  19. DATA B;
  20. FORMAT MSRP2 $20.;
  21. SET A;
  22. /* IF MSRP1 > 30000 THEN MSRP2 = "DAYU3W"; */
  23. /* ELSE MSRP2 = "XIAOYU1000"; */
  24. IF MSRP1 > 30000 THEN MSRP2 = "DAYU3W";
  25. ELSE IF MSRP1 > 1000 THEN MSRP2 = "DAYU3W<1000";
  26. ELSE IF MSRP1 > 200 THEN MSRP2 = "DAYU3W<200";
  27. ELSE MSRP2 = "XIAOYU1000";
  28. RUN;
  30. DATA A;
  31. KEEP MSRP1;
  32. SET SASHELP.CARS;
  33. MSRP1 = MSRP + 1;
  34. IF MSRP1 > 50000; 
  35. RUN;
  37. DATA A;
  38. KEEP MSRP1;
  39. SET SASHELP.CARS;
  40. MSRP1 = MSRP + 1;
  41. WHERE MSRP1 > 50000; 
  42. RUN;
  44. DATA C;
  45. KEEP MSRP1 MSRP2;
  46. SET SASHELP.CARS;
  47. MSRP1 = MSRP + 1;
  48. IF MSRP1 > 50000; 
  49. IF MSRP1 > 60000 THEN MSRP2 = ">";
  50. ELSE MSRP2 = "<";
  51. RUN;
  53. /* IF SUBSTR(A,1,1) = "1" */
  54. /* IF SUBSTR(A,1,1) = "1" OR SUBSTR(A,1,1) = "2" */
  55. /* IF SUBSTR(A,1,1) = "1" AND MSRP1 > 60000 */
  56. /* IF SUBSTR(A,1,1) IN ("1", "2") */
  57. /* IF SUBSTR(A,1,1) NOT IN ("1", "2") */
  58. /* "1" */
  59. /* "2" */
  60. /* "3" */
  62. DATA D;
  63. SET C C;
  64. RUN;
  66. PROC SORT DATA = D;
  67. BY MSRP2 MSRP1;
  68. RUN;
  70. PROC FREQ DATA = D;
  71. TABLE MSRP2 * MSRP1;
  72. RUN;

1.2.2.2 SAS拼表

  1. OPTIONS COMPRESS = YES;
  3. DATA TEST_X1;
  4. INPUT NAME $ PRODUCT $ TYPE $;
  5. CARDS;
  6. A CAR 40
  7. B CAR 42
  8. C BUS 44
  9. D MOTO 9
  10. E BUS 10
  11. ;
  12. RUN;
  14. DATA TEST_X2;
  15. INPUT NAME $ PRODUCT $ TYPE $;
  16. CARDS;
  17. A CAR 40
  18. C CAR 42
  19. B BUS 44
  20. H MOTO 9
  21. J BUS 10
  22. ;
  23. RUN;
  25. DATA TEST_Y;
  26. INPUT NAME $ NPRODUCT $ NTYPE $;
  27. CARDS;
  28. A APPLE 38
  29. B BANANA 42
  30. C CAT 44
  31. D DOG 9
  32. E EGG 10
  33. ;
  34. RUN;
  36. DATA TEST_Z;
  37. INPUT NAME $ NPRODUCT $ NTYPE $;
  38. CARDS;
  39. A APPLE 38
  40. A BANANA 42
  41. B CAT 44
  42. B DOG 9
  43. B EGG 10
  44. ;
  45. RUN;
  47. DATA A1_1;
  48. SET TEST_X1 TEST_X2;
  49. RUN;
  51. DATA A1_2;
  52. SET TEST_X1 TEST_Y;
  53. RUN;
  55. DATA A1_3;
  56. SET TEST_X1 TEST_Y(RENAME=(NPRODUCT=PRODUCT NTYPE=TYPE));
  57. RUN;
  59. /* DATA t3; */
  60. /* MERGE t1(IN=A) t2(IN=B); */
  61. /* BY n1; */
  62. /* IF A; */
  63. /* IF B; */
  64. /* RUN; */
  66. PROC SORT DATA = TEST_X2;BY NAME;RUN;
  67. PROC SORT DATA = TEST_Y;BY NAME;RUN;
  68. PROC SORT DATA = TEST_Z;BY NAME;RUN;
  70. DATA A2;
  71. MERGE TEST_X2(IN=A) TEST_Y(IN=B);
  72. BY NAME;
  73. IF A;
  74. RUN;
  76. DATA A2_1;
  77. MERGE TEST_X2(IN=A) TEST_Y(IN=B);
  78. BY NAME;
  79. IF NOT A;
  80. RUN;
  82. DATA A3;
  83. MERGE TEST_X2(IN=A) TEST_Y(IN=B);
  84. BY NAME;
  85. IF B;
  86. RUN;
  88. DATA A4;
  89. MERGE TEST_X2(IN=A) TEST_Y(IN=B);
  90. BY NAME;
  91. IF A AND B;
  92. RUN;
  94. DATA A5;
  95. MERGE TEST_X2(IN=A) TEST_Y(IN=B);
  96. BY NAME;
  97. RUN;
  99. DATA A51;
  100. MERGE TEST_X2(IN=A) TEST_Y(IN=B);
  101. BY NAME;
  102. IF A OR B;
  103. RUN;
  105. DATA A6;
  106. MERGE TEST_X2(IN=A) TEST_Y(IN=B);
  107. BY NAME;
  108. IF A;
  109. RUN;
  111. DATA A7;
  112. MERGE TEST_X2(IN=A) TEST_Z(IN=B);
  113. BY NAME;
  114. IF A;
  115. RUN;

1.2.2.3 SQL应用

  1. OPTIONS COMPRESS = YES;
  3. /* CREATE */
  4. PROC SQL;
  5. CREATE TABLE TCUSTR(
  6.     CUSTR_NBR VARCHAR(18),
  7.     SEX INT
  8. );
  9. QUIT;
  11. /* INSERT */
  12. PROC SQL;
  13. INSERT INTO TCUSTR(CUSTR_NBR, SEX)
  14.     VALUES("440101200109090011", 1)
  15.     VALUES("360101199901010012", 0);
  16. QUIT;
  18. /* DELETE */
  19. PROC SQL;
  20. DELETE FROM TCUSTR WHERE CUSTR_NBR = "440101200109090011";
  21. QUIT;
  23. /* UPDATE */
  24. PROC SQL;
  25. UPDATE TCUSTR SET SEX = 1 WHERE CUSTR_NBR = "360101199901010012";
  26. QUIT;
  28. /* SELECT */
  29. PROC SQL;
  30. SELECT * 
  31. FROM SASHELP.CARS;
  32. QUIT;
  34. PROC SQL;
  35. SELECT * 
  36. FROM SASHELP.CARS 
  37. WHERE MAKE = "Acura";
  38. QUIT;
  40. PROC SQL;
  41. SELECT MAKE, MSRP
  42. FROM SASHELP.CARS 
  43. WHERE MAKE = "Acura";
  44. QUIT;
  46. /* SQL FUNC */
  47. PROC SQL;
  48. SELECT 
  49.     COUNT(MSRP),
  50.     SUM(MSRP),
  51.     MAX(MSRP),
  52.     MIN(MSRP),
  53.     AVG(MSRP)
  54. FROM SASHELP.CARS 
  55. WHERE MAKE = "Acura";
  56. QUIT;
  58. PROC SQL;
  59. SELECT 
  60.     COUNT(MSRP) AS CNT_MSRP,
  61.     SUM(MSRP) AS SUM_MSRP,
  62.     MAX(MSRP) AS MAX_MSRP,
  63.     MIN(MSRP) AS MIN_MSRP,
  64.     AVG(MSRP) AS AVG_MSRP
  65. FROM SASHELP.CARS 
  66. WHERE MAKE = "Acura";
  67. QUIT;
  69. /* GROUP BY */
  70. PROC SQL;
  71. SELECT 
  72.     MAKE,
  73.     COUNT(MSRP) AS CNT_MSRP,
  74.     SUM(MSRP) AS SUM_MSRP,
  75.     MAX(MSRP) AS MAX_MSRP,
  76.     MIN(MSRP) AS MIN_MSRP,
  77.     AVG(MSRP) AS AVG_MSRP
  78. FROM SASHELP.CARS
  79. GROUP BY MAKE;
  80. QUIT;
  82. /* ORDER BY */
  83. PROC SQL;
  84. SELECT 
  85.     MAKE,
  86.     COUNT(MSRP) AS CNT_MSRP,
  87.     SUM(MSRP) AS SUM_MSRP,
  88.     MAX(MSRP) AS MAX_MSRP,
  89.     MIN(MSRP) AS MIN_MSRP,
  90.     AVG(MSRP) AS AVG_MSRP 
  91. FROM SASHELP.CARS
  92. GROUP BY MAKE
  93. ORDER BY CNT_MSRP;
  94. QUIT;
  96. /* WHERE */
  97. PROC SQL;
  98. SELECT 
  99.     MAKE,
  100.     COUNT(MSRP) AS CNT_MSRP,
  101.     SUM(MSRP) AS SUM_MSRP,
  102.     MAX(MSRP) AS MAX_MSRP,
  103.     MIN(MSRP) AS MIN_MSRP,
  104.     AVG(MSRP) AS AVG_MSRP
  105. FROM SASHELP.CARS
  106. WHERE MAKE ^= "Acura"
  107. GROUP BY MAKE
  108. ORDER BY CNT_MSRP;
  109. QUIT;
  111. /* HAVING */
  112. PROC SQL;
  113. SELECT 
  114.     MAKE,
  115.     COUNT(MSRP) AS CNT_MSRP,
  116.     SUM(MSRP) AS SUM_MSRP,
  117.     MAX(MSRP) AS MAX_MSRP,
  118.     MIN(MSRP) AS MIN_MSRP,
  119.     AVG(MSRP) AS AVG_MSRP
  120. FROM SASHELP.CARS 
  121. WHERE MAKE ^= "Acura"
  122. GROUP BY MAKE
  123. HAVING CNT_MSRP > 5
  124. ORDER BY CNT_MSRP;
  125. QUIT;
  127. /* CREATE TABLE */
  128. PROC SQL;
  129. CREATE TABLE CARS_GROUP AS 
  130. SELECT 
  131.     MAKE,
  132.     COUNT(MSRP) AS CNT_MSRP,
  133.     SUM(MSRP) AS SUM_MSRP,
  134.     MAX(MSRP) AS MAX_MSRP,
  135.     MIN(MSRP) AS MIN_MSRP,
  136.     AVG(MSRP) AS AVG_MSRP
  137. FROM SASHELP.CARS 
  138. WHERE MAKE ^= "Acura"
  139. GROUP BY MAKE
  140. HAVING CNT_MSRP > 5
  141. ORDER BY CNT_MSRP;
  142. QUIT;
  144. /* CASE WHEN */
  145. PROC SQL;
  146. CREATE TABLE CARS_GROUP AS 
  147. SELECT 
  148.     MAKE,
  149.     SUM(CASE WHEN ENGINESIZE > 3 THEN MSRP ELSE 0 END) AS SUM_MSRP
  150. FROM SASHELP.CARS 
  151. GROUP BY MAKE;
  152. QUIT;

1.2.2.4 PROC步

  1. OPTIONS COMPRESS = YES;
  3. DATA CARS;
  4. SET SASHELP.CARS;
  5. RUN;
  8. /* PROC SORT */
  9. PROC SORT DATA = SASHELP.CARS;
  10. BY MSRP;
  11. RUN;
  13. PROC SORT DATA = SASHELP.CARS OUT = CARS_MSRP;
  14. BY DESCENDING MSRP;
  15. RUN;
  16. PROC SORT DATA = SASHELP.CARS OUT = CARS_MAKE;
  17. BY MAKE;
  18. RUN;
  20. DATA CARS_DUP;
  21. KEEP TYPE ORIGIN DRIVETRAIN;
  22. SET SASHELP.CARS;
  23. RUN;
  25. PROC SORT DATA = CARS_DUP OUT = CARS_NDK NODUPKEY;
  26. BY TYPE ORIGIN;
  27. RUN;
  29. PROC SORT DATA = CARS_DUP OUT = CARS_NQK NOUNIQUEKEY;
  30. BY TYPE ORIGIN;
  31. RUN;
  33. PROC SORT DATA = CARS_DUP OUT = CARS_ND NODUP;
  34. BY TYPE ORIGIN;
  35. RUN;
  38. /* PROC FREQ */
  39. PROC FREQ DATA = CARS NOPRINT;
  40. TABLE DRIVETRAIN/OUT=CARS_FREQ;
  41. RUN;
  43. PROC FREQ DATA = CARS;
  44. TABLE DRIVETRAIN * ORIGIN/NOCOL NOROW NOPERCENT MISSING;
  45. RUN;
  46. PROC FREQ DATA = CARS;
  47. TABLE ORIGIN * DRIVETRAIN/NOCOL NOROW NOPERCENT MISSING;
  48. RUN;
  50. PROC FREQ DATA = CARS;
  51. TABLE DRIVETRAIN * ORIGIN;
  52. WHERE TYPE = "SUV";
  53. RUN;
  55. PROC FREQ DATA = CARS;
  56. TABLE TYPE * DRIVETRAIN * ORIGIN;
  57. RUN;
  59. PROC FREQ DATA = CARS;
  60. TABLE (TYPE DRIVETRAIN) * ORIGIN;
  61. /*    TABLE TYPE * ORIGIN;*/
  62. /*    TABLE DRIVETRAIN * ORIGIN;*/
  63. RUN;
  65. /*    PROC SORT DATA = ;*/
  66. /*    BY ;*/
  67. /*    RUN;*/
  68. /*    PROC FREQ DATA = ;*/
  69. /*    TABLE ;*/
  70. /*    RUN;*/
  71. /*    PROC UNIVARIATE DATA = ;*/
  72. /*    VAR ;*/
  73. /*    RUN;*/
  76. /* PROC UNIVARIATE */
  77. PROC UNIVARIATE DATA = CARS;
  78. VAR MSRP;
  79. RUN;
  80. PROC UNIVARIATE DATA = CARS;
  81. VAR MAKE;
  82. RUN;
  84. PROC UNIVARIATE DATA = CARS;
  85. VAR MSRP;
  86. CLASS MAKE;
  87. RUN;
  90. /* PROC TRANSPOSE */
  91. DATA CARS_MAMS;
  92. KEEP MAKE TYPE MSRP;
  93. SET SASHELP.CARS;
  94. RUN;
  96. PROC SORT DATA = CARS_MAMS OUT = CARS_MAMSD NODUPKEY;
  97. BY MAKE TYPE;
  98. RUN;
  100. PROC TRANSPOSE DATA = CARS_MAMSD;
  101. VAR MSRP;
  102. BY MAKE;
  103. RUN;
  105. PROC TRANSPOSE DATA = CARS_MAMSD OUT=CARS_M PREFIX=ID_;
  106. VAR MSRP;
  107. BY MAKE;
  108. ID TYPE;
  109. RUN;
  112. /* PROC SURVEYSELECT */
  113. PROC SURVEYSELECT 
  114.     DATA=CARS_MAMS METHOD=SRS N=3 
  115.     OUT=CARS_SRS_N3;
  116. RUN;
  117. PROC SURVEYSELECT 
  118.     DATA=CARS_MAMS METHOD=SRS SAMPRATE=0.1 
  119.     OUT=CARS_SRS_P1;
  120. RUN;
  122. PROC SURVEYSELECT 
  123.     DATA=CARS_MAMS METHOD=SRS SAMPRATE=0.1 
  124.     OUT=CARS_SRS_N1;
  125. STRATA MAKE;
  126. RUN;

1.2.2.5 SAS进阶

  1. OPTIONS COMPRESS = YES;
  3. DATA CARS;
  4. KEEP MAKE MSRP; 
  5. SET SASHELP.CARS;
  6. RUN;
  8. PROC SORT DATA = CARS OUT = CARS_MSRP;
  9. BY DESCENDING MSRP;
  10. RUN;
  12. PROC FREQ DATA = CARS_MSRP NOPRINT;
  13. TABLE MAKE/OUT = CARS_FMAKE;
  14. RUN;
  16. /* SAS FORMAT */
  17. DATA DEMOY;
  18. INPUT NAME $11. BIRTH HEIGHT;
  19. INFORMAT BIRTH YYMMDD10. HEIGHT 5.1;
  20. CARDS;
  21. LIXIAO      1959/10/21 170.5 
  22. WANGMING    1992/02/21 177.8
  23. ;
  24. RUN;
  25. /*1960/01/01*/
  26. /*1991/10/21 11616 */
  27. /*1991/10/23 11618 */
  29. DATA DEMON;
  30. INPUT NAME $11. BIRTH $11. HEIGHT;
  31. CARDS;
  32. LIXIAO      1991/10/21 170.5
  33. WANGMING    1992/02/21 177.8
  34. ;
  35. RUN;
  37. DATA DDATE;
  38. SDATE = "01JAN2018"D;
  39. F1DATE = SDATE;
  40. F2DATE = SDATE;
  41. F3DATE = SDATE;
  42. RUN;
  44. DATA DDATE;
  45. SDATE = "01JAN2018"D;
  46. FORMAT F1DATE YYMMDD10. F2DATE YYMMDD8. F3DATE YYMMDD6.;
  47. F1DATE = SDATE;
  48. F2DATE = SDATE;
  49. F3DATE = SDATE;
  50. RUN;
  52. /* SAS DATE */
  53. DATA SASDATE;
  54. SDATE = "21DEC2018"D;
  55. STIME = "09:39:00"T;
  56. SDATETIME = "21DEC2018 09:39:00"DT;
  57. RUN;
  59. DATA SASDATE_TR;
  60. SDATE = "21DEC2018"D;
  61. STIME = "09:39:00"T;
  62. SDATETIME = "21DEC2018 09:39:00"DT;
  64. FORMAT F1DATE YYMMDD10. F2DATE YYMMDD8. FTIME TIME10. FDATETIME DATETIME20.;
  65. F1DATE = SDATE;
  66. F2DATE = SDATE;
  67. FTIME = STIME;
  68. FDATETIME = SDATETIME;
  69. RUN;
  71. /* INTNX */
  72. DATA DINTNX;
  73. SDATE = "01JAN2018"D;
  74. FORMAT FDATE SDATED SDATEM SDATEY YYMMDD10.;
  75. FDATE = SDATE;
  76. SDATED = INTNX("DAY", SDATE, 1);
  77. SDATEM = INTNX("MONTH", SDATE, 1); 
  78. SDATEY = INTNX("YEAR", SDATE, 1);
  79. RUN;
  81. DATA DINTNXN;
  82. SDATE = "02JAN2018"D;
  83. FORMAT FDATE SDATEMB SDATEMM SDATEME SDATEMS YYMMDD10.;
  84. FDATE = SDATE;
  85. SDATEMB = INTNX("MONTH", SDATE, 1, "B");
  86. SDATEMM = INTNX("MONTH", SDATE, 1, "M"); 
  87. SDATEME = INTNX("MONTH", SDATE, 1, "E");
  88. SDATEMS = INTNX("MONTH", SDATE, 1, "S");
  89. RUN;
  91. DATA DINTNXN;
  92. SDATE = "02JAN2018"D;
  93. FORMAT FDATE SDATEMB SDATEMM SDATEME SDATEMS YYMMDD10.;
  94. FDATE = SDATE;
  95. SDATEMB = INTNX("YEAR", SDATE, 1, "B");
  96. SDATEMM = INTNX("YEAR", SDATE, 1, "M"); 
  97. SDATEME = INTNX("YEAR", SDATE, 1, "E");
  98. SDATEMS = INTNX("YEAR", SDATE, 1, "S");
  99. RUN;
  101. /* INTCK */
  102. DATA DINTCK;
  103. SDATE_M11 = "01DEC2018"D;
  104. SDATE_M12 = "05NOV2018"D;
  105. GAP_DAYS = INTCK("DAY", SDATE_M12, SDATE_M11);
  106. GAP_MONS = INTCK("MONTH", SDATE_M12, SDATE_M11);
  107. RUN;
  109. /* PUT INPUT */
  110. DATA DPUT;
  111. DATE18 = 20181221;
  112. DATEP18 = PUT(DATE18, $8.);
  113. RUN;
  115. DATA DPUT;
  116. DATE18 = 20181221;
  117. DATEP18 = PUT(DATE18, $8.);
  118. DATEI18 = INPUT(DATEP18, YYMMDD8.);
  119. /*    DATEI18 = INPUT(PUT(DATE18, $8.), YYMMDD8.);*/
  120. DATEPN18 = PUT(DATEI18, YYMMDDN8.);
  121. DATEIN18 = INPUT(DATEPN18, 8.);
  122. RUN;

1.2.2.6 SAS宏

  1. OPTIONS COMPRESS = YES;
  3. DATA CARS;
  4. KEEP MAKE MODEL MSRP;
  5. SET SASHELP.CARS;
  6. RUN;
  8. DATA DVAR01;
  9. SET CARS;
  10. WHERE MAKE = "BMW";
  11. RUN;
  12. DATA DVAR02;
  13. SET CARS;
  14. WHERE MAKE = "BMW";
  15. RUN;
  16. DATA DVAR03;
  17. SET CARS;
  18. WHERE MAKE = "BMW";
  19. RUN;
  21. %LET VAR1 = BMW;
  22. DATA DVAR01;
  23. SET CARS;
  24. WHERE MAKE = "&VAR1.";
  25. RUN;
  26. DATA DVAR02;
  27. SET CARS;
  28. WHERE MAKE = "&VAR1.";
  29. RUN;
  30. DATA DVAR03;
  31. SET CARS;
  32. WHERE MAKE = "&VAR1.";
  33. RUN;
  34. /* ... */
  35. %PUT &VAR1.;
  37. /* Acura */
  38. /* BMW */
  39. %LET VAR1 = BMW;
  40. DATA DVAR01;
  41. SET CARS;
  42. WHERE MAKE = "&VAR1.";
  43. RUN;
  45. /* 1 */
  46. %LET VAR1 = Acura;
  47. /*    %PUT &VAR1.;*/
  48. DATA DVAR1;
  49. SET CARS;
  50. WHERE MAKE = "&VAR1.";
  51. RUN;
  53. /* 2 */
  54. DATA _NULL_;
  55. CALL SYMPUT("VAR2", "Acura");
  56. RUN;
  57. /* %PUT &VAR2.; */
  58. DATA DVAR2;
  59. SET CARS;
  60. WHERE MAKE = "&VAR2.";
  61. RUN;
  63. /* 3 */
  64. /*    PROC SQL NOPRINT;*/
  65. /*    SELECT MAKE INTO: VAR3 */
  66. /*    FROM CARS;*/
  67. /*    QUIT;*/
  68. /*    PROC SQL NOPRINT;*/
  69. /*    SELECT MAX(MAKE) INTO :VAR3 */
  70. /*    FROM CARS;*/
  71. /*    QUIT;*/
  72. PROC SQL NOPRINT;
  73. SELECT MAX(MAKE),MIN(MSRP) INTO :VAR3, :VAR4 
  74. FROM CARS;
  75. QUIT;
  76. %PUT &VAR3. &VAR4.;
  78. DATA DVAR3;
  79. SET CARS;
  80. WHERE MAKE = "&VAR3.";
  81. RUN;
  83. %MACRO T1;
  84.     DATA A;
  85.     RUN;
  86.     %MEND T1;
  87. %T1;
  89. %MACRO T2(V=,);
  90.     %PUT &V.;
  92.     DATA DVAR_&V.;
  93.     SET CARS;
  94.     WHERE MAKE = "&V.";
  95.     /* 100H */
  96.     RUN;
  97. %MEND T2;
  98. %T2(V=BMW);
  99. /*    %PUT &V.;*/
  100. %T2(V=Acura);
  101. /*    %PUT &V.;*/
  104. %LET M = BMW;
  105. %MACRO T3(V=,);
  106.     %PUT &V.;
  107.     %IF &V.= &M. %THEN %DO;
  108.         DATA DVAR_&V.;
  109.         SET CARS;
  110.         WHERE MAKE = "&V.";
  111.         /* 100H */
  112.         RUN;
  113.         /*            ...*/
  114.     %END;
  115.     %ELSE %DO;
  116.         DATA DVAR_&V.;
  117.         SET CARS;
  118.         WHERE MAKE = "&V." AND MSRP > 50000;
  119.         /* 100H */
  120.         RUN;
  121.         /*            ...*/
  122.     %END;
  123. %MEND T3;
  124. %T3(V=BMW);
  125. %T3(V=Acura);
  127. /*    DATA A1;*/
  128. /*    SET A;*/
  129. /*    IF MSRP > 100 THEN A1 = 1;*/
  130. /**/
  131. /*    IF MSRP > 100 THEN DO;*/
  132. /*        A1 = 1;*/
  133. /*        A2 = 1;*/
  134. /*    END;*/
  135. /*    RUN;*/
  137. DATA A;
  138. DO I = 1 TO 10;
  139. T = I + 2;
  140. OUTPUT;
  141. END;
  142. RUN;
  144. %MACRO T4(V=,);
  145.     %PUT &V.;
  147.     %DO I = 0 %TO &V.;
  148.         DATA A_&I.;
  149.         /*            SET ACCT;*/
  150.         /*            WHERE MONTH = &I.; */
  151.         RUN;
  152.     %END;
  153. %MEND T4;
  154. %T4(V=10);
  155. %T4(V=2);
  158. %MACRO T5(V=,);
  159.     %PUT &V.;
  161.     DATA MSRP;
  162.     SET CARS;
  163.     %DO I = 0 %TO &V.;
  164.         IF MSRP > &I.*10000 THEN A&I. = 1;ELSE A&I. = 0;
  165.     %END;
  166.     RUN;
  167. %MEND T5;
  168. %T5(V=10);
  171. /*EVAL*/
  172. %LET S = 1;
  173. %LET I = 10;
  174. %PUT &S. &I.;
  176. %LET S = &S. + &I.;
  177. %PUT &S. &I.;
  179. %LET S = 1;
  180. %LET I = 10;
  181. %LET S = %EVAL(&S. + &I.);
  182. %PUT &S. &I.;

1.2.3 SAS书籍

《Learning SAS by Example: A Programmer’s Guide》 《SAS编程技术教程（朱世武）》 《SAS编程与数据挖掘商业案例》 《SAS语言抛砖引玉》 《The Little SAS Book》 SAS Help and Documentation

1.2.4 SAS Help使⽤

image.png
如上SAS书籍，专门强调了SAS Help and Documentation的重要性。
使用方法也有很多：

1.2.4.1 通篇阅读

相信这个跟全文背诵一样恐怖，但跟着官方文档走，可更全面感受SAS产品设计。
如Contents就提供这样的阅读目录，可一览SAS Products.

image.png

1.2.4.2 关键字搜索

相信是最常用的方法，搜索框输入关键字，快速搜索。
如输入substr
image.png

当然，也有缺点，尤其个别奇奇怪怪的关键字，如输入index
image.png
搜索结果令人无所适从。

1.2.4.3 归纳体系

关键字搜索非常有用，但当你忘记关键字拼写、拼错关键字甚至想要某某功能关键字时，归纳体系更加便捷。
（1）Category
以计算函数为例，搜索Function Categories

Functions can be categorized by the types of values that they operate on. Each DS2 function belongs to one of the following categories:

  • Array
    operates on a named aggregate collection of homogenous data
  • Bitwise Logical Operations
    operates on one or more bit patterns or binary numbers at the level of their individual bits
  • Character
    operates on character data and SQL expressions
  • Date and Time
    operates on date and time values
  • Descriptive Statistics
    operates on values that measure central tendency, variation among values, and the shape of distribution values
  • Distance
    returns the geodetic distance
  • Financial
    calculates financial values such as interest, periodic payments, depreciation, and prices for European options on stocks.
  • Hyperbolic
    performs hyperbolic calculations such as sine, cosine, and tangent
  • Mathematical
    operates on values to perform general mathematical calculations
  • Numeric
    operates on numeric values
  • Probability
    returns probability calculations.
  • Quantile
    returns a quantile from specific distributions
  • Random Number
    returns random variates from specific distributions
  • Special
    operates on null values and SAS missing values, suspends execution of a program, specifies numeric informats at run time, and executes a FedSQL statement.
  • Trigonometric
    operates on values to perform trigonometric calculations
  • Truncation
    operates on values to limit the number of digits
  • Variable Information
    operates on variables and returns names, types, lengths, informats, labels, and other variable information

Array为例，共计如下4个函数，
截屏2020-12-09 下午2.14.35.png
点进DIM函数，也能发现所在Category。
image.png
（2）See Also
一般地，查看某函数时，文末都有一段See Also，带出类似函数。
以DIM函数为例，就把该Category下的其他类似函数链接出来。
image.png

1.2.4.4 总结

整体来说，SAS Help and Documentation的编写还是有一定水平的，始终是最主要的SAS学习文档。需要使用者在应用过程中，不断学习总结，相信SAS水平会有更加长足的进步。此外，除了SAS Help外，剩余资源主要聚焦在人大经济论坛部分早期文档。这里可以看到，SAS整体开发社区还是比较小，后续笔者将再做整理，欢迎持续关注。
如有更新，可能会在 https://github.com/IvanaXu/DataSAS

1.2.5 SAS IDE

1.2.5.1 SAS-BASE

Win + R，输入sas即可调出SAS BASE，页面如下：
image.png

  • 逻辑库-浏览创建SAS库管理、浏览SAS库文件（移动、复制、更名和删除）、建立非SAS文件的快捷方式；
  • 输出结果-以树形结构展示提交SAS程序输出的各项结果，查看、存储、打印或删除各项结果的内容；
  • 编辑器-对SAS程序语法检查、程序段的收缩和展开、可记录宏、自定快捷键；
  • 日志页面-基本窗口，缺省地打开，依次记录SAS进程中各程序运行的信息，可用命令清空；

这里指出，SAS BASE相比SAS EG，语法要求更严谨、没有代码提示、需手动查看日志、不支持中文变量，对代码要求更高，在非远程连接模式下，一般我们建议使用SAS BASE。

1.2.5.2 SAS-EG

SAS Enterprise Guide基本使用教程，特指远程连接模式：
（1）桌面双击打开或开始菜单中打开SAS Enterprise Guide 7.1(64-bit)；
（2）用户登录
（2.1）若提示情况如下：
image.png
输入用户名、密码，点击“确认”即可。
（2.2）若提示情况如下：
image.png
点击“是”，进入连接属性，如下。
image.png
（2.3）若无配置文件列表，则点击“添加”。
image.png
（2.4）在设置中填写名称（可自定义），远程连接服务器的地址、端口、登录的用户名、密码等。填写完毕后点击“保存”，后续步骤与已有配置文件的步骤（2.5）一致。
（2.5）若已有配置文件，则选择给定的特定文件，点击“设为活动”，将弹出用户登录界面。
image.png
输入给定的用户名和密码，点击“确定”。
image.png
点击关闭后即可。
（3）程序编写
（3.1）点击左上角文件，新建，程序。
image.png
（3.2）在右侧增强型编辑器区域编写代码，如下。
image.png
（3.3）代码编写完毕后，点击“运行”，或通过键盘快捷键F3或F8，运行程序。
（3.4）若需执行部分代码，选中要执行部分的代码，点击“运行”，或通过键盘快捷键F3或F8，运行程序。

1.2.5.3 SAS-EM

此部分教程选自《SAS EM使用介绍——刘洋洋》
远程连接模式下，
（1）基本操作
image.png
（2）新建项目
登录后界面如下图，如果已经建立过项目则选择“打开项目”，新建项目则选择“新建项目”，注意：新建项目请放置在个人目录下。
image.png
（3）新建逻辑库
类似sas base中的“libname”功能，加载了逻辑库，才能够提取数据。
加载逻辑库的两种方式如下图。其中逻辑库路径即分析数据的目录。
image.png

image.png
（4）创建流程图、创建数据源
创建方式如下图，新创建的流程图是空白的。
创建数据源后才可以对数据进行分析。
image.png

image.png
（5）代码编写窗口
SAS EM中有类似base的代码编写功能，在这里可以做一些数据查看和处理。
image.png
（6）建模功能

详见 模型策略部分。

1.2.5.4 SAS-University

https://www.sas.com/en_us/software/university-edition/download-software.html

SAS大学生版本，通过VirtualBox的轻量级（2G）SAS版本，支持Windows、MacOS、Linux，安装后启动，可在浏览器通过SAS Studio/JupyterLab编写、运行代码，非常适用于SAS学习、示例代码开发。
安装步骤：
截屏2020-12-14 下午3.05.01.png
开启页面：
输入浏览器上输入http://0.0.0.0:10080/
截屏2020-12-12 下午6.55.11.png
JupyterLab：
与python-Jupyter一致，易保存易共享，本文后续SAS代码大部分经此工具校验调试。
截屏2020-12-12 下午6.58.23.png
未来可期！

特别的，由于依托Linux环境，SAS路径一般在 /opt/sasinside/SASHome/SASFoundation/9.4/sas，且SAS版本为9.4_M6： 截屏2021-07-25 上午10.22.35.png 9.4_M6的特殊性在于调整SAS中文编码字符数由2至3，因SUBSTR(“堂”,1,1) = SUBSTR(“商”,2,1) 这种偶然性而影响SAS中文判断的bug将大幅减少！ 请多关注SAS版本修订。

1.2.6 SAS代码规范

  • SAS命名规范（试行）

（1）命名由数字、英文字母、下划线组成，且由英文字母开头，不区分大小写，不使用中文命名；
（2）命名总长度不超过32个字符长度，若过长可调整部分缩写，如CRED_LIMIT转换CREDL；
（3）命名不使用常见代码关键词，如SQL、RUN、DATA等；
（4）计算逻辑，如A/B表示为A_PCT_B；
（5）特定前缀命名，如判断是否IS、统计函数类：SUM、MAX、MIN、AVG等、列表LIST、时间类：DT（具体时间）、DAY（天数）等、金额类AMT、数量类CNT、占比类PCT；
（6）特定后缀命名，如时间类（默认为账单月）：前一个月L01（LAST）、后一个月N01（NEXT），如产品类，分期产品ZDFQ、LHFQ等；
（7）特定意义命名，如银联命名习惯（CUSTR-客户）、分期MP、账龄MOB等；
（8）尽量使用英文命名，减少使用中文拼音全拼，避免使用中文拼音缩写；

PS：部分SAS版本也是支持中文命名的，很方便也很好理解，但会无限制使命名过长；

1.2.7 SAS⽚段

以下摘录部分常用SAS代码片段。


～逻辑库链接（仅作示例）

  1. %MACRO CONNECT_LIBNAME();
  2. LIBNAME ODS greenplm dsn="Greenplum Wire Protocol" user="readuser" pass=readuser schema=sdm;
  3. LIBNAME MD greenplm dsn="Greenplum Wire Protocol" user="readuser" pass=readuser schema=adm;
  4. LIBNAME RPT greenplm dsn="Greenplum Wire Protocol" user="readuser" pass=readuser schema=rpt;
  5. LIBNAME BSD "/data2/userhome/DEV-HOME/SAS-DEV-OUTBOUND-01/Data"
  6. %MEND CONNECT_LIBNAME;
  7. %CONNECT_LIBNAME();

～格式刷

  1. %MACRO FMT(TB=, KEY=, DESC=, FMT_NAME=);
  2. PROC SORT DATA = &TB. NODUPKEY OUT = INDATA;
  3. BY &KEY. &DESC.;
  4. QUIT;
  6. PROC CONTENTS DATA = INDATA OUT = CONT NOPRINT;RUN;
  7. PROC SQL NOPRINT;
  8. SELECT TYPE INTO: KEY_TYPE FROM CONT
  9. WHERE UPCASE(NAME) = UPCASE("&KEY.");
  10. QUIT;
  12. %PUT &KEY_TYPE.;
  13. DATA &FMT_NAME.;
  14. LENGTH LABEL $200.;
  15. SET INDATA END = LAST;
  16. START = COMPRESS(&KEY.);
  17. LABEL = COMPRESS(&DESC.);
  18. RETAIN FMTNAME "&FMT_NAME.";
  19. %IF &KEY_TYPE. = 2 %THEN TYPE = "C";
  20. %ELSE TYPE = "N";
  21. ;
  22. OUTPUT;
  23. IF LAST THEN DO;
  24. START = "**OTHER**";
  25. LABEL = "UNKNOWN";
  26. HLO = "O";
  27. OUTPUT;
  28. END;
  29. RUN;
  31. PROC FORMAT CNTLIN = &FMT_NAME.;RUN;
  32. %MEND FMT;
  34. %FMT(TB=SASHELP.CARS, KEY=MODEL, DESC=MAKE, FMT_NAME=FMT_MODEL);

～程序休眠

  1. /* 程序将休眠1h */
  2. %MACRO CALL_SLEEP(HOURS=1);
  3.     DATA _NULL_;
  4.     CALL SLEEP(&HOURS., 3600);
  5.     RUN;
  6. %MEND;
  7. %CALL_SLEEP();

～条件构建宏

根据筛选条件构建如MSRP IN(90520)的宏变量，用于where语句。

  1. %MACRO COND_CRE(SRC_LIB=, SRC_TB=, TARGET_VAR=, INCOND=, OUTCOND=, MAXSIZE=1000);
  2. %GLOBAL &OUTCOND.;
  4. PROC SORT 
  5.     DATA = &SRC_LIB..&SRC_TB.(WHERE = (&INCOND.))
  6.     OUT = TEMP(KEEP = &TARGET_VAR.) NODUPKEY;
  7. BY &TARGET_VAR.;
  8. RUN;
  10. PROC TRANSPOSE DATA = TEMP OUT = T_TEMP PREFIX = A;
  11. VAR &TARGET_VAR.;
  12. RUN;
  14. DATA T2_TEMP;
  15. SET T_TEMP;
  16. ARRAY ARR1(*) A:;
  17. LENGTH COND $&MAXSIZE..;
  18. COND = "";
  19. DO I = 1 TO DIM(ARR1);
  20.     COND = COMPRESS(COND)||COMPRESS(ARR1[I])||',';
  21. END;
  22. COND = COMPRESS(COND);
  23. COND = SUBSTR(COND,1,LENGTH(COND)-1);
  24. COND = "&TARGET_VAR. IN("||COMPRESS(COND)||")";
  25. KEEP COND;
  26. RUN;
  28. PROC SQL NOPRINT;
  29. SELECT MAX(COND) INTO: &OUTCOND. FROM T2_TEMP;
  30. QUIT;
  32. %MEND COND_CRE;
  34. %COND_CRE(
  35.     SRC_LIB=SASHELP, SRC_TB=CARS, 
  36.     TARGET_VAR=MSRP, INCOND=MSRP>90000, 
  37.     OUTCOND=_T, MAXSIZE=1000
  38. );
  40. PROC PRINT DATA = SASHELP.CARS;
  41. WHERE &_T.;
  42. RUN;
  43. %PUT &_T.;
  44. /* MSRP IN(90520,94820,121770,126670,128420,192465) */

～FTP下载/上传文件

  1. /* RF */
  2. %MACRO RF(FILENAME=,OUTFILE=);
  3.     FILENAME &OUTFILE. FTP "&FILENAME." CD="/test/"
  4.     USER="ip1" PASS="xuyifan" HOST="127.0.0.1" PROMPT;
  5. %MEND RF;
  7. %RF(FILENAME=run_all.csv,OUTFILE=RM);    
  9. /* RF, 数据读取 */
  10. DATA TA;
  11. %LET _EFIERR = 0;
  12. INFILE RM
  13. DELIMITER = "," MISSOVER DSD LRECL=32767 FIRSTOBS=1 ENCODING="UTF-8" TERMSTR=LF;
  14. FORMAT A B $20.;
  15. INPUT 
  16. A $
  17. B $
  18. ;
  19. RUN;
  22. /* WF */
  23. %MACRO WF(FILENAME=,OUTFILE=);
  24.     FILENAME &OUTFILE. FTP "&FILENAME." CD="/test/result/"
  25.     USER="ip1" PASS="xuyifan" HOST="127.0.0.1" PROMPT;
  26. %MEND WF;
  28. %WF(FILENAME=TA.csv,OUTFILE=WM);
  30. /* WF, 数据写入 */
  31. DATA _NULL_;
  32. SET TA;
  33. FILE WM LRECL=32767 DLM="@" ENCODING="UTF-8";
  34. PUT 
  35. A
  36. B
  37. ;
  38. RUN;

～月度循环

  1. %MACRO RCYC(ST=, ED=,);
  2.     DATA _NULL_;
  3.     CALL SYMPUT("DEV", INTCK("MONTH", INPUT("&ST.01", YYMMDD8.), INPUT("&ED.01", YYMMDD8.)));
  4.     RUN;
  5.     %PUT &DEV.;
  7.     %DO I=0 %TO &DEV.;
  8.         DATA _NULL_;
  9.         CALL SYMPUT("LMON", PUT(INTNX("MONTH", INPUT("&ST.01", YYMMDD8.), &I.-1),YYMMN6.));
  10.         CALL SYMPUT("NMON", PUT(INTNX("MONTH", INPUT("&ST.01", YYMMDD8.), &I.+0),YYMMN6.));
  11.         CALL SYMPUT("TMON", PUT(INTNX("MONTH", INPUT("&ST.01", YYMMDD8.), &I.+1),YYMMN6.));
  12.         RUN;
  13.         %PUT &I. &LMON. &NMON. &TMON.;
  14.     %END;
  15. %MEND;
  16. %RCYC(ST=201810, ED=201812);
  17. /*
  18. 2
  19. 0 201809 201810 201811
  20. 1 201810 201811 201812
  21. 2 201811 201812 201901
  22. */

～DO OVER

  1. DATA A;
  2. INPUT X Y $ Z $ M;
  3. CARDS;
  4. . . . 1
  5. 1 A B 4
  6. ;
  7. RUN;
  9. DATA B;
  10. SET A;
  11. ARRAY CHAR _CHARACTER_;
  12. ARRAY NUM _NUMERIC_;
  13. DO OVER CHAR;
  14.     IF CHAR EQ " " THEN CHAR = "/";
  15. END;
  16. DO OVER NUM;
  17.     IF NUM EQ . THEN NUM = 0;
  18. END;
  19. RUN;
  21. PROC PRINT DATA = B;
  22. RUN;
  23. /*
  24. Obs    X    Y    Z    M
  25. 1    0    /    /    1
  26. 2    1    A    B    4
  27. */

～格式化加强理解

  1. /* 简单 */
  2. PROC FORMAT;
  3.     VALUE RR
  4.         LOW-50 = "(LOW,50]"
  5.         50<-60 = "(50,60]"
  6.         60<-HIGH = "(60,HIGH)"
  7.     ;
  8. RUN;
  10. PROC FORMAT;
  11.     VALUE $RRR
  12.         "男" = "1"
  13.         "女" = "2"
  14.     ;
  15. RUN;
  17. PROC FREQ DATA = SASHELP.CLASS;
  18.     TABLE HEIGHT SEX;
  19.     FORMAT HEIGHT RR. SEX $RRR.;
  20. RUN;
  22. /* 复杂 */
  23. %MACRO FMT(TABLE=, VAR=);
  24.     PROC SUMMARY DATA = &TABLE. NWAY MISSING;
  25.         CLASS RK_&VAR.;
  26.         VAR &VAR.;
  27.         OUTPUT OUT = SUMM(DROP = _TYPE_ _PREQ_) MIN = VAR_MIN MAX = VAR_MAX;
  28.     RUN;
  29.     PROC SORT DATA = SUMM;BY RK_&VAR.;RUN;
  30.     DATA SUMM;
  31.         SET SUMM;
  32.         BY RK_&VAR.;
  33.         RK_&VAR. = _N_;
  34.     RUN;
  36.     PROC SORT DATA = SUMM;BY DESCENDING RK_&VAR.;RUN;
  37.     DATA FMT;
  38.         SET SUMM END = LAST;
  39.         RETAIN EEXCL "Y" SEXCL "N";
  40.         START = PUT(VAR_MIN, 8.2);
  41.         END = PUT(LAG(VAR_MIN), 8.2);
  42.         IF _N_ EQ 1 THEN END = "HIGH";
  43.         IF LAST THEN START = "LOW";
  44.         FMTNAME = "&VAR.";
  45.         LABEL = CATS(PUT(RK_&VAR., Z2.), "(", START, "-", END, ")");
  46.     RUN;
  47.     PROC FORMAT CNTLIN = FMT;RUN;
  48. %MEND;
  50. PROC RANK DATA = SASHELP.CLASS GROUPS = 4 OUT = RK;
  51.     VAR HEIGHT;
  52.     RANKS RK_HEIGHT;
  53. RUN;
  55. %FMT(TABLE=RK, VAR=HEIGHT);
  57. PROC FREQ DATA = SASHELP.CLASS;
  58.     TABLE HEIGHT;
  59.     FORMAT HEIGHT HEIGHT.;
  60. RUN;

截屏2020-12-13 下午1.55.23.png

～PROC SUMMARY

  1. PROC SUMMARY DATA=SASHELP.CLASS NWAY MISSING;
  2.     CLASS AGE;
  3.     VAR HEIGHT;
  4. OUTPUT OUT = Q N= MEAN= SUM=/AUTONAME;
  5. RUN;
  7. PROC PRINT DATA = Q;
  8. RUN;
  10. /* PROC SUMMARY DATA = RES2018M3 NWAY MISSING;
  11.     CLASS KDT IA TA MA
  12.     ;
  13.     VAR COM03 COM06 COM12 RCRED_LMT;
  14.     OUTPUT OUT = SUN2018M3(DROP = _TYPE_ RENAME = (_FREQ_ = NUM)) SUM=;
  15. RUN; */

截屏2020-12-13 下午2.03.21.png

～哈希表HASH连接

  1. DATA TMP;
  2.     SET SASHELP.CLASS(OBS=2);
  3.     LIFT = _N_;
  4.     KEEP AGE LIFT;
  5. RUN;
  7. DATA H_LIFT;
  8.     SET SASHELP.CLASS;
  9.     * 进行初始化;
  10.     IF _N_ EQ 0 THEN SET TMP;
  11.     IF _N_ EQ 1 THEN
  12.         DO;
  13.             DECLARE HASH H(DATASET: "TMP");
  14.             H.DEFINEKEY("AGE");
  15.             H.DEFINEDATA("LIFT");
  16.             H.DEFINEDONE();
  17.         END;
  18.     * IF H.FIND() EQ 0 THEN OUTPUT;
  19.     IF H.FIND() EQ 0 THEN H_LIFT=LIFT;
  20.     ELSE CALL MISSING(H_A);
  21.     * ELSE H_LIFT=0;
  22.     DROP LIFT;
  23. RUN;

～排列组合

  1. %LET N = 3; 
  2. PROC SQL NOPRINT;
  3.     SELECT QUOTE(STRIP(NAME)) INTO :NAMES SEPARATED BY " "
  4.     FROM SASHELP.CLASS(OBS = &N.);
  5. QUIT;
  6. %PUT &NAMES.;
  8. %MACRO ALL_COMB;
  9.     PROC DATASETS LIB = WORK NOLIST NODETAILS;
  10.         DELETE APP_ALL_COMB;
  11.     RUN;
  13.     %DO I = 1 %TO &N.;
  14.         DATA ALL_COMB;
  15.             LENGTH VARS $488.;
  16.             ARRAY ARR(&N.) $32 (&NAMES.);
  17.             NCOMB = COMB(DIM(ARR), &I.);
  18.             DO I = 1 TO NCOMB;
  19.                 CALL ALLCOMB(I, &I., OF ARR(*));
  21.                 VARS = CATX(" ",OF ARR1-ARR&I.);
  22.                 KEEP VARS;
  23.                 OUTPUT;
  24.             END;
  25.         RUN;
  26.         PROC APPEND 
  27.             BASE = APP_ALL_COMB 
  28.             DATA = ALL_COMB;
  29.         RUN;
  30.     %END;
  31. %MEND;
  32. %ALL_COMB;
  34. PROC PRINT DATA = APP_ALL_COMB;
  35. RUN;

截屏2020-12-13 下午2.32.56.png

～Lift提升度

  1. PROC SQL NOPRINT;
  2.     SELECT MEAN(HEIGHT) INTO :MEAN FROM SASHELP.CLASS;
  3. QUIT;
  5. %PUT &MEAN.;
  6. /* 62.33684 */
  8. PROC SQL;
  9.     CREATE TABLE LIFT AS
  10.     SELECT 
  11.         AGE,
  12.         MEAN(HEIGHT) AS MEAN,
  13.         MEAN(HEIGHT)/&MEAN. AS LIFT
  14.     FROM SASHELP.CLASS
  15.     GROUP BY 1
  16.     ORDER BY 2 DESC;
  17. QUIT;

～PROC SQL

  1. PROC SQL;
  2.     CREATE TABLE TMP1 AS
  3.     SELECT 
  4.         AGE,
  5.         COUNT(*)/19 AS PCT
  6.     FROM SASHELP.CLASS
  7.     GROUP BY 1
  8.     ORDER BY 2 DESC;
  9. QUIT;
  11. PROC SQL;
  12.     CREATE TABLE TMP2 AS
  13.     SELECT 
  14.         AGE,
  15.         COUNT(*)/19 AS PCT
  16.     FROM SASHELP.CLASS
  17.     GROUP BY AGE
  18.     ORDER BY PCT DESC;
  19. QUIT;
  21. PROC SQL;
  22.     CREATE TABLE TMP3 AS
  23.     SELECT 
  24.         AGE,
  25.         COUNT(*)/19 AS PCT FORMAT PERCENT10.2
  26.     FROM SASHELP.CLASS
  27.     GROUP BY AGE
  28.     ORDER BY PCT DESC;
  29. QUIT;

～SAS绘图

  1. PROC RANK DATA = SASHELP.CLASS GROUPS = 4 OUT = RK;
  2.     VAR HEIGHT;
  3.     RANKS RK_HEIGHT;
  4. RUN;
  6. * 修改系统默认MAXOBS
  7. ODS GRAPHICS ON/MAXOBS=10000000; 
  8. PROC SGPLOT DATA = RK;
  9.     VBAR 
  10.         RK_HEIGHT/RESPONSE = RK_HEIGHT 
  11.         STAT = FREQ DATALABEL;
  12.     VLINE 
  13.         RK_HEIGHT/RESPONSE = HEIGHT 
  14.         STAT = MEAN Y2AXIS DATALABEL DATALABELPOS = BOTTOM;
  15. RUN;
  16. ODS GRAPHICS OFF;
  18. * VBAR 柱状图;
  19. * VLINE 折线图;
  20. * STAT 统计量;
  21. * DATALABEL 标签;
  22. * DATALABELPOS 标签位置;
  23. * Y2AXIS 次坐标;

截屏2020-12-13 下午2.47.06.png

～自制数据集

  1. DATA VARL;
  2.     FORMAT VARS $40. VART $40. VARC $100.;
  3.     INFILE DATALINES DELIMITER = "|";
  4.     INPUT VARS $ VART $ VARC $;
  5. DATALINES;
  6. ACTIVE_MOB|BASE|POINTS=21/41
  7. AGENCY_VISE_SUM|MEAN_6M|POINTS=O,INC=1
  8. ;
  10. PROC PRINT DATA = VARL;
  11. RUN;

截屏2020-12-13 下午2.51.49.png

～宏变量工厂

  1. DATA CARS;
  2. SET SASHELP.CARS(OBS=3);
  3. RUN;
  5. %MACRO M();
  6.     DATA _NULL_;
  7.         SET CARS NOBS = N;
  8.         CALL SYMPUT(COMPRESS("VARS_"||_N_), COMPRESS(MSRP));
  9.         CALL SYMPUT("NAL", N);
  10.     RUN;
  11.     %DO I = 1 %TO &NAL.;
  12.         %PUT &&VARS_&I..;
  13.     %END;
  14. %MEND M;
  15. %M();
  16. /*
  17.     36945
  18.     23820
  19.     26990
  20. */

～判断表是否存在

  1. %MACRO M();
  2.     %IF %SYSFUNC(EXIST(SASHELP.CARS))
  3.             %THEN %PUT SASHELP.CARS EXIST;
  4.     %ELSE %PUT SASHELP.CARS NOT EXIST;
  5. %MEND M;
  6. %M();
  7. /* SASHELP.CARS EXIST */

～KS计算

  1. DATA TA;
  2. SET SASHELP.CARS;
  3. IF CYLINDERS IN (4,6) THEN K = 0;
  4. ELSE K = 1;
  5. KEEP MSRP K;
  6. RUN;
  8. PROC SORT DATA = TA;
  9. BY MSRP;
  10. RUN;
  12. PROC RANK DATA = TA OUT = TB GROUPS = 5;
  13. VAR MSRP;
  14. RANKS GMSRP;
  15. RUN;
  17. PROC FREQ DATA = TB NOPRINT;
  18. TABLE GMSRP * K/OUT = TC;
  19. RUN;
  21. PROC NPAR1WAY DATA = TC KS NOPRINT;
  22. CLASS K;
  23. VAR GMSRP;
  24. FREQ COUNT;
  25. OUTPUT OUT = TD;
  26. RUN;
  28. PROC PRINT DATA = TD(KEEP = _D_);
  29. RUN;
  30. /* 0.59527 */

～批量SQL(宏)

  1. %MACRO PSQL(PARA1=, PARA2=,);
  2.     SUM(CASE WHEN UPCASE(TYPE) = "&PARA1." THEN 1 ELSE 0 END) AS CNT_&PARA1.,
  3.     SUM(CASE WHEN UPCASE(TYPE) = "&PARA1." THEN &PARA2. ELSE 0 END) AS SUM_&PARA1._&PARA2.,
  4.     MAX(CASE WHEN UPCASE(TYPE) = "&PARA1." THEN &PARA2. ELSE 0 END) AS MAX_&PARA1._&PARA2.,
  5. %MEND PSQL;
  7. PROC SQL;
  8. CREATE TABLE TEMP(DROP = _T) AS 
  9. SELECT
  10.     MAKE,
  11.     %PSQL(PARA1=SUV, PARA2=MSRP)
  12.     %PSQL(PARA1=SUV, PARA2=INVOICE)
  13.     %PSQL(PARA1=SPORTS, PARA2=MSRP)
  14.     %PSQL(PARA1=SPORTS, PARA2=INVOICE)
  15.     1 AS _T
  16. FROM SASHELP.CARS(OBS=100)
  17. GROUP BY MAKE;
  18. QUIT;
  20. PROC PRINT DATA = TEMP;
  21. RUN;

1.2.8 SAS编译

经典问题（简化自《SAS培训_PDV精华》）：

交易流水表EVENT月切表，数据字段：账户号XACCOUNT、交易时间INP_DAY、交易金额BILL_AMT，已根据XACCOUNT、INP_DAY排序。 限一个SAS DATA步，求每个账户首次达到消费金额1288的时间以及当月累计消费金额。 数据集示例：

  1. DATA EVENT;
  2.     FORMAT XACCOUNT $8. INP_DAY BILL_AMT;
  3.     INFILE DATALINES DELIMITER = "|";
  4.     INPUT XACCOUNT $ INP_DAY BILL_AMT;
  5. DATALINES;
  6. 00000001|20200101|1200
  7. 00000001|20200102|120
  8. 00000001|20200108|302
  9. 00000002|20200101|323
  10. 00000002|20200102|423
  11. 00000002|20200110|135
  12. 00000003|20200101|3232
  13. 00000003|20200101|544
  14. 00000003|20200107|423
  15. 00000003|20200109|135
  16. ;
  17. /* 已排序 */
  18. PROC SORT DATA = EVENT;
  19. BY XACCOUNT INP_DAY;
  20. RUN;

求解过程：

  1. DATA RESULT1(KEEP = XACCOUNT RAMT1 RDAT1) RESULT2;
  2. SET EVENT(IN = E);
  3. BY XACCOUNT;
  4. PUT "S0:" _ALL_;
  6. IF FIRST.XACCOUNT THEN DO;
  7.     RAMT1 = 0;
  8.     RAMT2 = 0;
  9.     RAMT3 = 0;
  10.     RDAT1 = 99999999;
  11.     RDAT2 = 99999999;
  12. END;
  14. RAMT1 + BILL_AMT;
  16. RETAIN RAMT2;
  17. RAMT2 = SUM(RAMT2, BILL_AMT);
  19. RAMT3 = SUM(RAMT3, BILL_AMT);
  21. RETAIN RDAT1;
  22. IF RAMT1 >= 1288 THEN RDAT1 = MIN(INP_DAY, RDAT1);
  24. IF RAMT1 >= 1288 THEN RDAT2 = MIN(INP_DAY, RDAT2);
  26. PUT "S1:" _ALL_;
  27. IF LAST.XACCOUNT
  28.     THEN OUTPUT RESULT1;
  29. OUTPUT RESULT2;
  30. RUN;

输出结果：

  1. S0:E=1 XACCOUNT=00000001 INP_DAY=20200101 BILL_AMT=1200 FIRST.XACCOUNT=1 LAST.XACCOUNT=0 RAMT1=0 RAMT2=. RAMT3=. RDAT1=. RDAT2=.
  2. _ERROR_=0 _N_=1
  4. S1:E=1 XACCOUNT=00000001 INP_DAY=20200101 BILL_AMT=1200 FIRST.XACCOUNT=1 LAST.XACCOUNT=0 RAMT1=1200 RAMT2=1200 RAMT3=1200
  5. RDAT1=99999999 RDAT2=99999999 _ERROR_=0 _N_=1
  7. S0:E=1 XACCOUNT=00000001 INP_DAY=20200102 BILL_AMT=120 FIRST.XACCOUNT=0 LAST.XACCOUNT=0 RAMT1=1200 RAMT2=1200 RAMT3=. RDAT1=99999999
  8. RDAT2=. _ERROR_=0 _N_=2
  10. S1:E=1 XACCOUNT=00000001 INP_DAY=20200102 BILL_AMT=120 FIRST.XACCOUNT=0 LAST.XACCOUNT=0 RAMT1=1320 RAMT2=1320 RAMT3=120
  11. RDAT1=20200102 RDAT2=20200102 _ERROR_=0 _N_=2
  13. S0:E=1 XACCOUNT=00000001 INP_DAY=20200108 BILL_AMT=302 FIRST.XACCOUNT=0 LAST.XACCOUNT=1 RAMT1=1320 RAMT2=1320 RAMT3=. RDAT1=20200102
  14. RDAT2=. _ERROR_=0 _N_=3
  16. S1:E=1 XACCOUNT=00000001 INP_DAY=20200108 BILL_AMT=302 FIRST.XACCOUNT=0 LAST.XACCOUNT=1 RAMT1=1622 RAMT2=1622 RAMT3=302
  17. RDAT1=20200102 RDAT2=20200108 _ERROR_=0 _N_=3

截屏2020-12-15 下午2.13.38.png

显然，我们在RAMT1、RDAT1上把要求逻辑实现了一遍，且PUT出来了DATA执行前后_ALL_的变化。
这些输出意味着什么呢？在回答此问题之前，需要先了解SAS数据步的编译。

数据步的编译和执行过程： 编译过程： 在这个阶段，系统扫描每个语句检查它是否有语法错误。大部分语法错误导致系统无法对数据步作进一步的处理，在编译阶段将建立要创建的数据集的描述部分。

执行阶段： 若数据步编译成功，就要开始执行阶段。在这个阶段对源数据文件的每一条记录都执行一次数据步，除非在程序中指明其他处理方式。在这个阶段建立数据集的数据部分。

具体来说，
（1）数据步的编译阶段：

  • 即对程序进行词语和语法编译梦，检查它是否有语法错误；

如，漏掉或错拼的关键词、无效的变量名、遗漏或错误的符号、无效的选择项；

  • 将程序转换为机器码，供执行阶段使用；
  • 建立工作部件输入缓冲器（Input Buffer）；
  • 建立工作部件PDV（程序数据列，Program Data Vector）；

用于建立SAS系统数据集，一次只处理一个观测，PDV一般格式为[_N_|_ERROR_]，
其中_N_记录DATA步执行的次数，_ERROR_指示出错信息，0为无错误，1为有错误；

  • 建立数据集中每个变量的三个必须的属性：Name、Type、Length；
  • 建立新建数据集的描述部分；

即数据集名、观测数和变量个数、变量名及其属性；

（2）数据步的执行阶段：
即创建数据集的数据部分，执行顺序：

  • PDV中外部变量初始化为缺省值；
  • 输入每条记录至输入缓冲器，按INPUT语句读至PDV（如有）；
  • 按数据步的其他语句处理后，存入PDV；
  • 在数据步结束时缺省地将PDV内容作为一条新观测写入新的数据集；
  • 回到数据步的开始，使PDV中外部变量初始化为缺省值；
  • 对源文件中每条记录都按上述步骤执行一次；
  • 当对源文件最后一条记录执行结束后，数据步执行完成；

上述步骤中，在数据步迭代开始时外部变量初始化为缺省值，但以下几种情况不受限制：

  • RETAIN语句；
  • SUM语句（A + 1）；
  • 数组_TEMPORARY_；
  • FILE、INFILE语句；
  • 自动生成变量，如_ERROR_、_N_等；

PS：上述变量自然不包含宏变量，宏变量声明后记录在SASHELP.VMACRO中。
如果还是觉得PDV太过抽象，可以这么理解：

个人认为可以把PDV想象成一排用于存放变量值的盒子。 每个盒子代表一个变量，提交一个DATA步后，SAS会对这个DATA步进行编译，然后执行。 首先，PDV是在DATA步的编译阶段生成的。（编译会进行语法检查并创建一排整齐摆放的”盒子”；）然后，在DATA步的执行阶段，根据不同语句对PDV中变量的值进行清空或更改。（将盒子清空或换上新的物品；） 最后，在RUN语句或者OUTPUT语句将PDV中变量的当前值输出到目标数据集中。KEEP、DROP语句或KEEP=、DROP=数据集选项会影响输出到目标数据集中变量的个数。（如果没有KEEP、DROP，将新建变量和数据集变量对应的盒子搬出到目标数据集；如果只有KEEP，则只搬KEEP指定的盒子；如果只有DROP，则不搬DROP指定的盒子；如果KEEP/DROP同时存在，则只搬KEEP-DROP后剩下的盒子） ——选自网络文章《SAS中关于PDV的总结》

所以，上例在我们通过PUT _ALL_查看PDV中所有变量后，发现：RAMT1、RAMT2实现了累加，而未RETAIN的RAMT3、RDAT2在S0时总是先被初始化，而S1每次仅计算当前所在行数据结果。至此，问题解决，有兴趣的同学可以进一步拓展SAS PROC步、宏编译。

在此感谢上述代码贡献者、搬运工们。