Hi all, I already have a %macro statement so that I only have to enter the beginning year and month, and also end year and end month I want everytime I want to run the file. However, I was facing a problem in the proc sql code. The code is correct but I want it to be inside the loop that depends on the beginning and end date I set. There are 4 different proc sql codes below, I want the code that highlighted in orange to be variable that depends on the date I set.   For Ex, the beg date I set here is 201812 and the end date is 201902, For the first and second proc sql code,i want it to be run 3 times but different accmth_fy which is 1,2 and 3 (since there is 3 months from 201812 to 201902)   For the third ans forth proc sql code, I want the code to run 3 times .Not only pl201812.POLA_clm_agg_201812 but also (pl201901.POLA_clm_agg_201901) and  (pl201890.POLA_clm_agg_201902). which is 3 months also.   It is impossible for me to list down all the same code for these 4 different proc sql if the beginning date I set is 201812 until the end date which is 201911. that will be total of 44 codes for my 4 different proc sql.   AND, the code must be work for if I only set for one month, if I set the beginning date and end date is the same. For Ex : 201812.   How can I loop for the proc sql since I already have %macro in my code?                     %macro date(Beg_YYYY=2018, Beg_MM=12,End_YYYY=2019,End_MM=02,Val_YYYY=2019,Val_MM=02);%global accYR accMTH;     %if &End_MM = 12 %then %do; %let accYR=%eval (End_YYYY+1) %let accMTH = %eval(&End_MM + 1); %end;   　 %else %do; %let accYR = &End_YYYY; %let accMTH = %eval(&End_MM+1);     %end;   　 　 libname pl&Val_YYYY&MM. "\\kaiwksgh415thw5\Data\POLA\Claim\&Val_YYYY\&Val_YYYY&MM\DBF"; Data pl&Val_YYYY&MM..POLA_clm_month_&Val_YYYY&MM.; set pl&Val_YYYY&MM..POLA_clm_agg_&Val_YYYY&MM.; where accyr_fy=&accYR. & accmth_fy<=&accMTH.; /***financial basis***/ dev11yr_fy=min(accyr_fy-lossyr_fy,11);   run; PROC FORMAT; value $ class 'VEH'='1' 'FIR'='2' 'GMS'='3' 'BON'='3' 'HUL'='3' 'PAC'='4' 'TEL'='5' 'WWC'='5' 'LIA'='6' 'CGO'='7' 'ENG'='8'   ; RUN;   /************************************************************/ PROC SQL; create table SMCD_PD AS SELECT put(zmajrsk,$class.) as class, dev11yr_fy,sum(zclmpd) AS zclmpd   FROM pl201902.POLA_clm_month_201902 WHERE accmth_fy=1 /****FINANCIAL BASIS*****/ GROUP BY class,2;   quit; ods html file="\\kaiwksgh415thw5\Data\POLA\Claim\2019\201902\result\SMCD_PD_Jan_10DY_201902.html"     /********************************************************************/ 　 PROC SQL; create table SMCD_PDNET AS SELECT put(zmajrsk,$class.) as class, dev11yr_fy,sum(zclmpdnet) AS zclmpdnet   FROM pl201902.POLA_clm_month_201902 WHERE accmth_fy=1 GROUP BY class,2;   quit; ods html file="\\kaiwksgh415thw5\Data\POLA\Claim\2019\201902\result\SMCD_PDNET_month_FY_10DY_201902.html"   style=normal; proc print; run;   　 /****************************************************************************/ 　 　 libname pl201812 "\\kaiwksgh415thw5\Data\POLA\Claim\2018\201812\DBF";   Data pl201812.POLA_clm_agg_201812; set pl201812.POLA_clm_agg_201812; run; 　 PROC SQL; create table SMCD_OS AS SELECT put(zmajrsk,$class.) as class, min(2019-lossyr_fy,11) as devyr,sum(zclmos) AS zclmos   FROM pl201812.POLA_clm_agg_201812 GROUP BY class,2;   quit; ods html file="\\kaiwksgh415thw5\Data\POLA\Claim\2019\201902\result\SMCD_OS_month_FY_10DY_201902.html"   style=normal; proc print; run; 　 　 　 　 　 　 　 /*****************************************************************/ PROC SQL; create table SMCD_OSNET AS SELECT put(zmajrsk,$class.) as class, min(2019-lossyr_fy,11) as devyr,sum(zclmosnet) AS zclmosnet   FROM pl201812.POLA_clm_agg_201812 GROUP BY class,2;   quit; ods html file="\\kaiwksgh415thw5\Data\POLA\Claim\2018\201812\result\SMCD_OSNET_month_FY_10DY_201812.html"   style=normal; proc print; run; 　 　 　 %mend date; %date(Beg_YYYY=2018, Beg_MM=12,End_YYYY=2019,End_MM=02,Val_YYYY=2019,Val_MM=.2); run;     ... View more

Hi all, I wanted to extract some words from the data column so that I can classified them in the new column based on some words (but not all) from the old column.     For example, in my old column, ZMAJCLDSC CGO - Marine Cargo                  (I want to extract CMC here)          ENG - Engineering                      (I want to extract the word ENG here) PAB                                              (I want to extract the first alphabet P here) PMD                                               (I want to extract the first alphabet P here) CBA                                               (I want to extract the first alphabet C here) IAB                                                (I want to extract the word IAB here)   then I will classified it based on what I have extract. ZMAJCLDSC  (OLD COLUMN)                                 lob  (NEW COLUMN) CGO - Marine Cargo                                                   cargo ENG - Engineering                                                       ENG PAB                                                                               PA PMD                                                                              PA CBA                                                                               PA IAB                                                                                  PA     I plan to use if statement based on what I extracted. here's my first two code below .But I dun know how to extract the words. What can I do with this?   if zmajcldsc='CGO - Marine Cargo' then lob='cargo';   else if zmajcldsc='ENG - Engineering' then lob='Engineering';       ... View more

Hi all,   I have a lot of excel csv files that have same columns name with different data, however some of the file have same column name also but in different order. This cause the data is messed when I import into sas. For Ex,   original file 1 fruits                 vegetable                  number               date orange                  tomato                     5                       10/6/2019 apple                     corn                       10                       5/5/2019   original file 2   number                      date                   fruits                 vegetable 6                            1/1/2019               watermelon          cabbage  8                             22/2/1997              kiwi                    carrot       original file 3 date                     number                    vegetable           fruits 8/8/2016                 44                        asparagus             durian 7/4/2006                 99                           eggplant             banana   the column order is not the same for almost every csv file.     if I put my code like below, the data from original file 2 and 3 does not follow the column name when import.   libname HAPPY "D:\school"; Data HAPPY.school; %let _EFIERR_=0; infile "D:\school\file\fruitandvegelist.csv" dlm=',' MISSOVER DSD lrecl=32767 firstobs=2;   　 informat fruits $4.; informat vegetable $4.; informat number 4.; informat date yymmdd10.;   format fruits $4.; format vegetable $4.; format number 4.; format date yymmdd10.;   input fruits $ vegetable $ number date;   run;   the data for orginal file 2 will import the number as fruits column, date as vegetable column and so on. it will follow the order in the code I type. How can I solve this problem? I want the data automatically follow the column name ... View more