How could I read these date variables using proc SQL

[Mirisage]

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

[art297 · Accepted solution]

If you don't use min you would have to include country in your group or else you will obtain 6 rather than 2 records.

Why don't you think you are reading the dates?  They appear to be read, just not formatted.

Does the following do what you want?

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;

[tish]

When you use aggregate functions, you are creating new variables, even if you are using the same names. As a result, you need to associate date formats with the date variables. The following works:

libname fromweb " someplace or other";

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

         fromweb.post_this_data

      group by

         account_number

      ;

quit;

Hope this helps.

[Mirisage]

Hi Art and Tish,

Thank both of you for this reply which is exactly what I was looking for.

Hi Art,

If I don't use "min" in front of "county" then could I include country in "group statement" like this or you meant some other way?

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, country

     ;

quit;

[art297]

@Mirisage: Yes!  And, if all of each country record contains the same string (including case), then (while I can't test it at the moment) I would think that you would obtain the same result as the one you had run.

[Mirisage]

Hi Art,

Thank you very much for this help.

Regards

Mirisage