Hi SAS Forum, Another worker has written below compacted code. /*COMPACTED CODE*/ %let age0=1303; %let yymm=1303 1304 1305 1306 1307  ; proc sql; create table OUR_age0 as select               a.*,                      b.LOAN_LIMIT,                      b.DEPOSIT,                      "&age0" as year_month from   (select       a.*                      from   LOCATION.FILE_1    as             a inner join                ABCD.INCOME_&age0  as             b on                a.ACT_NO=b.ACCOUNT_NUMBER                      where b.STATUS^="CLOSED")    as            a inner join                      ABCD.EXPENSES_&age0      as b on a.ACT_NO=b.ACCOUNT_NUMBER ; quit; /*I HAVE SPLIT ABOVE COMPACTED CODE INTO simple 2 PIECES*/ /*PIECE 1 – YELLOW PORTION ABOVE*/ Proc sql; Create table any_name as select        a.*               from   LOCATION.FILE_1      as            a inner join                      ABCD.INCOME_&age0    as            b on                      a.ACT_NO=b.ACCOUNT_NUMBER                      where b.STATUS^="CLOSED"; quit; run; /*PIECE 2*/ proc sql; create table OUR_age0_testing as select               a.*,                      b.LOAN_LIMIT,                      b.DEPOSIT,                      "&age0" as year_month from   any_name             as            a inner join        ABCD.EXPENSES_&age0        as            b on a.ACT_NO=b.ACCOUNT_NUMBER ; Quit; Run; /*QUESTION*/ Final files produced by the compacted code and split two pieces have to be identical but following shows it is not. proc compare base=Our_age0_testing compare=Our_age0 ;run; Could anyone help me to split the compacted code correctly? Thanks ... View more

Hi SAS Forum, I was trying to understand how does the proc sql sub query function (I never used sub querying). Could you please let me know what is the problem in the code below. proc sql;     create table anyname as select          a.*                      ,b.*                                 from       Table1          a                      ,Table2          b              where a.acctid not IN (select b.acctid); quit; Error messege comes like below: 15         proc sql; 16             create table dunga as select 17                         a.* 18                         ,b.* 19              from    a1   a 20                         ,a2   b 21                      where a.patid not IN (select b.patid);                                                         _                                                         22                                                         76 ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?,               AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH,               LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.  ERROR 76-322: Syntax error, statement will be ignored. Thanks, Mirisa ... View more

/*Hi SAS Forum, I have the table named "one"*/ data one; input trt patid visit dose; cards; 0 101 1 10 0 101 2 20 1 102 1 15 1 102 2 20 ; run; /*I wanted to extract the field "trt" from above table and at the same time I wanted to create a brand new variable named "which_year".  The values of field "which_year" should get 1, 2 and 3 in the three tables being created. Below macro does the job correctly.*/ %macro any (year_number); proc sql;     create table Year_&year_number as                 select          trt,                                 &year_number as which_year                       from    one               ; quit; %mend; %any (1); %any (2); %any (3); /*Now I have two tables like below and wanted to do a left join and wanted to do the same thing above   */ data one; input trt patid visit dose; cards; 0 101 1 10 0 101 2 20 1 102 1 15 1 102 2 20 ; run; data two; input patid sex $ 5-6 ; cards; 101 F 102 M 103 M 105 F ; run; /*Below code does the job correctly */ %macro any (year_number); proc sql;     create table Year_&year_number as select                 a.*  ,                 b.sex                       from    one              a left join              two              b                 on                 a.patid=b.patid; quit; %mend; %any (1); %any (2); %any (3); Question: In the yellow highlighted table above, can’t I create a brand new variable named "which_year" which has values 1, 2 and 3 in the three tables being created.? Thanks Mirisa ... View more

Hi SAS Forum, I have below data set. data acct_li; input old_lmt authorized_limit ; cards; 700 1000 800 1500 5000 5000 100 110 7000 6000 ; run; A previous worker has applied the follwoing code on the above data set.  data t_review1;      set acct_li ;      retain ActNum limitIcs 0;      if (authorized_limit>old_lmt)   then do;            ActNum+1;            limitIcs=sum(limitIcs, (authorized_limit-old_lmt));            output;      end; run; proc print; run; Above code produced the following output. old_limit  authorized_limit   ActNum   limitIcs /*these are the table headings*/ 700 1000 1 300 800 1500 2 1000 100 110 3 1010 Q: Of the above code, I cannot understand how the below sum function portion is happening. limitIcs=sum(limitIcs, (authorized_limit-old_lmt)); The red color "limitIcs" in the above sum function was never calculated before, but in the sum function we are asking the fucntion to add    limitIcs to (authorized_limit-old_lmt); How come it happens? Please help me to understand it. Thanks Mirisa ... View more

Hi SAS Forum, To show my proc sort question, I have taken the below data set from SUGI paper 037-30 of Britta Kelsey, and is acknowledged. data best; input patient 1-2 arm $ 4-5 bestres $ 6-7 delay 9-10; datalines; 01 A CR 0 02 A PD 1 03 B PR 1 04 B CR 2 05 C SD 1 06 C SD 3 07 C PD 2 01 A CR 0 03 B PD 1 ; run; I have seen in many papers nodupkey in proc sort is used like in mtheod 1 below but my boss is always using it like in method 2. I have checked using proc compare and found that identical resutls are produced by both methods. Method 1: proc sort data=best  nodupkey out=ex1; by arm patient; run; Method 2: proc sort data=best out=ex2 nodupkey; by arm patient; run; proc compare base=ex1 compare=ex2; run; Question: 1. Do both methods produce identical results for larger data sets too? 2. Which method is efficient? Could some one help me. Thanks Mirisa ... View more

Hi SAS Forum, I did a literature survey to understand the difference between “If-then-do-end” vs. %if %then %do %end. Below conditional logic code (SUGI29 paper 243-29 by Slaughter and Delwiche acknowledged) prints data set called “orders” if the current day is Monday. And creates a table using proc tabulate if the current day is Friday. %MACRO reports; %IF &SYSDAY = Monday %THEN %DO; PROC PRINT DATA = orders NOOBS; FORMAT OrderDate DATE7.; TITLE "&SYSDAY Report: Current Orders"; %END; %ELSE %IF &SYSDAY = Friday %THEN %DO; PROC TABULATE DATA = orders; CLASS CustomerID; VAR Quantity; TABLE CustomerID ALL, Quantity; TITLE "&SYSDAY Report: Summary of Orders"; %END; %MEND reports; RUN; %reports RUN; Q: Why cannot we simply replace all the places having %IF %THEN %DO %END and %ELSE %IF %THEN %DO %END with standard IF THEN DO END and ELSE IF THEN DO END ? Sorry if my question looks like a stupid one. Miris ... View more

Hi Sas Forum, I Have the attahced k_1 data set. I wanted to create a flag with the following condition. if STATUS in("CURRENT") then Delq_Flag=0; else Delq_Flag=1; So, I used below code. data k_3; set k_1; if STATUS in("CURRENT") then Delq_Flag=0; else Delq_Flag=1; run; Q: But above code doesn't give correct results although the logic seems correct. But below code provides the intended resutls. I cannot understand what is happening. Coudl someone help me to understand. data k_2; set k_1; if index(STATUS,"CURRENT") then Delq_Flag=0; else Delq_Flag=1; run; Thanks Miris ... View more