Hi Forum, I have this dataset. data CCCC; input yearmonth age; cards; 201112 10 201012 20 201207 30 ; run; I need to pick the latest vlaue of variable named yearmonth (which is 201207) and feed that value into sql code below. /*SQL code*/ proc sql;   select age   from anyname   where yearmonth = '&latest';   run; My approach  proc sort data=CCCC out= anyname;       by descending yearmonth;     run;     data _null_;       set anyname;       if _n_=1 then do;         call symput('latest',yearmonth);         stop;       end;     run; problem An error messege comes. ERROR: Expression using equals (=) has components that are of different data types. Could anyone please help me to fix this. Thanks Mirisage ... View more

Hi EG Community, When I open my EG, I get “Project Tree” box in the left top corner of the screen. In this box, under “Programs”, the names of my SAS “programs” are displayed. Each program name is preceded by a “Running man” icon. Some of these icons have “Exclamation mark”, while some doesn’t have. Could anyone please let me know if there is a specific reason for having  “Exclamation mark” in some “Running man” icons, and not having it in some other icons. Thanks Mirisage ... View more

Hi SAS Forum, I have the attached data set. I have got the sum of variables named “principal” and “write_off_total” by account_number using below sql code. (Art and Tish have helped me to introduce date formatting in this sql code). proc sql;      create table want as      select      min(bank_number)             as bank_number       ,      min (ACCOUNT_NUMBER)        as ACCOUNT_NUMBER    ,      min(country)                 as country           ,      min(branch_number)           as branch_number    ,      min(currency_code)           as currency_code   ,      max (POST_DATE)              as POST_DATE    Format=date9.     ,      max (EFFECTIVE_DATE)         as EFFECTIVE_DATE Format=date9.     ,      sum (PRINCIPAL)              as sum_PRINCIPAL     ,      sum (Write_off_Total)        as net_Write_off_Total           from b.post_this_data      group by account_number      ; quit; Background: Account_number and Branch_number are numerics. I need to convert them to character variables. What I have done, I have converted them using following two steps after the above sql extraction. data want; set want; char_account_number = put(account_number, $15.) ; drop account_number ; rename char_account_number=account_number ; run; data want; set want; char_branch_number = put(branch_number, $7.) ; drop branch_number ; rename char_branch_number=branch_number ; run; Questions: i). I wonder if this procedure is correct ? I noted the value moved to middle of cell in “account_number” variable and moved to the left hand corner of the cell in “Branch_number” variable after this conversion (suspicious!). ii). Can we do this conversion at the sql extraction process itself? Thanks Mirisage ... View more

Hi SAS Community, I have two tables (Have_1 and Have_2). data Have_1; informat Current_date date9.; Input Current_date   Account_number $12-14 Arrears_Band $ 17-23  balance ; Format Current_date date9.; cards; 28FEB2010  111  NPNA    10 31MAR2010  111  Current 20 31MAY2010  111  30 - 60 30 28FEB2010  444  Current 40 31MAR2010  444  30 - 60 50 30APR2010  444  30 - 60 60 31MAY2010  444  Current 70 30APR2010  555  Current 80 31MAY2010  555  NPNA    90 31AUG2011  555  90 +    12 30SEP2011  555  NPNA    0 31MAY2010  666  Current 15 31AUG2011  666  90 +    20 30SEP2011  666  Current 30 ; run; data Have_2; informat Post_date date9.; Input Post_date Account_number 12-14 Net_Write_off_total_USD; Format Post_date date9.; cards; 31AUG2010  111  50 30SEP2011  555  77 ; run; Question: I need to join these 2 tables so that I could get the answer below. Answer: /*    Final data set should be like this Notice the 2 records from have_2 table are embedded within have_1 table in date sequence. And a value called “writoff” was assigned. 28FEB2010  111  NPNA    10 31MAR2010  111  Current 20 31MAY2010  111  30 - 60 30 31AUG2010  111  writoff 50 28FEB2010  444  Current 40 31MAR2010  444  30 - 60 50 30APR2010  444  30 - 60 60 31MAY2010  444  Current 70 30APR2010  555  Current 80 31MAY2010  555  NPNA    90 31AUG2011  555  90 +    12 30SEP2011  555  NPNA    0 30SEP2011  555  writoff 77 31MAY2010  666  Current 15 31AUG2011  666  90 +    20 30SEP2011  666  Current 30                      */ My approach: /*Transform unique ID from numeric to character*/ data have_2; set have_2; char_account_number = put(account_number, $7.) ; drop account_number ; rename char_account_number=account_number ; run; /*Carrying out a full outer join*/ proc sql;             select             a.*                     ,             b.* from    have_1                a full join         have_2                      b on a.Account_number=b.Account_number; run; Obviously this approach doesn’t give me what I want. I really appreciate if someone could help me? Thanks Mirisage ... View more

Hi Colleagues, I have the attached data set. I have tried to get the sum of variables named “principal” and “write_off_total” by account_number using this code. proc sql;      create table want as      select      min(bank_number)           as bank_number       ,      min (ACCOUNT_NUMBER) as ACCOUNT_NUMBER    ,      min(country)               as country           ,      min(branch_number)      as branch_number    ,      min(currency_code)      as currency_code   ,      max (POST_DATE)            as POST_DATE         ,      max (EFFECTIVE_DATE)       as EFFECTIVE_DATE    ,      sum (PRINCIPAL)            as sum_PRINCIPAL     ,      sum (Write_off_Total)   as net_Write_off_Total           from b.post_this_data      group by account_number      ; quit; Questions: i). I cannot get the dates read using this code ii). Variable “country” is just the name of the country. So, does “min” in the statement  below has any sense? min(country)               as country           , If I do not use adjective "min", is it wrong? Could someone help me please? Thanks Mirisage ... View more

Hi Community, I have the attached Excel data set. I imported it as a SAS data set using “File”, “Import Data” menu of SAS. Two variables (post_date and effective_date) in the imported SAS dataset are in datetime format but I need to change the format into date9. format. What I have done? In the import process SAS generated a code. I have taken it from the log. Then I tweaked (highlighted in yellow below), and re-ran. DATA WORK.post_this1;     LENGTH         NUMBER             8         CODE             $ 3         NUMBER_2           8         CURRENCY           8         AT_NUMBER          8         ATT_NAME         $ 3         DUCT_CODE          8         R_CODE             8         POST_DATE          8         EFFECTIVE_DATE     8         TXN                8         TYPE             $ 28         BASIC              8         OFF                8 ;     FORMAT         NUMBER           BEST12.         CODE             $CHAR3.         NUMBER_2         BEST12.         CURRENCY         BEST12.         AT_NUMBER        BEST12.         ATT_NAME         $CHAR3.         DUCT_CODE        BEST12.         R_CODE           BEST12.         POST_DATE        DATE9. /*originally this was DATETIME18.*/         EFFECTIVE_DATE   DATE9. /*originally this was DATETIME18.*/         TXN              BEST12.         TYPE             $CHAR28.         BASIC            COMMA12.         OFF              COMMA12. ;     INFORMAT         NUMBER           BEST12.         CODE             $CHAR3.         NUMBER_2         BEST12.         CURRENCY         BEST12.         AT_NUMBER        BEST12.         ATT_NAME         $CHAR3.         DUCT_CODE        BEST12.         R_CODE           BEST12.         POST_DATE        DATETIME18.         EFFECTIVE_DATE   DATETIME18.         TXN              BEST12.         TYPE             $CHAR28.         BASIC            COMMA12.         OFF              COMMA12. ;     INFILE 'T:\SASWork\_TD12012\#LN00044'         LRECL=84         ENCODING="WLATIN1"         TERMSTR=CRLF         DLM='7F'x         MISSOVER         DSD ;     INPUT         NUMBER           : BEST32.         CODE             : $CHAR3.         NUMBER_2         : BEST32.         CURRENCY         : BEST32.         AT_NUMBER        : BEST32.         ATT_NAME         : $CHAR3.         DUCT_CODE        : BEST32.         R_CODE           : BEST32.         POST_DATE        : BEST32.         EFFECTIVE_DATE   : BEST32.         TXN              : BEST32.         TYPE             : $CHAR28.         BASIC            : BEST32.         OFF              : BEST32. ; RUN; Question Formats of post_date and effective_date are not changed to DATE9. Could someone help me? Thanks Mirisage ... View more