Help using Base SAS procedures

How could I read these date variables using proc SQL

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

How could I read these date variables using proc SQL

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

Attachment

Accepted Solutions
Solution
‎07-30-2012 03:31 PM
PROC Star
Posts: 7,471

Re: How could I read these date variables using proc SQL

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;

View solution in original post


All Replies
Solution
‎07-30-2012 03:31 PM
PROC Star
Posts: 7,471

Re: How could I read these date variables using proc SQL

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;

Contributor
Posts: 52

Re: How could I read these date variables using proc SQL

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.

Super Contributor
Posts: 338

Re: How could I read these date variables using proc SQL

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;

PROC Star
Posts: 7,471

Re: How could I read these date variables using proc SQL

@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.

Super Contributor
Posts: 338

Re: How could I read these date variables using proc SQL

Hi Art,

Thank you very much for this help.

Regards

Mirisage

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 215 views
  • 3 likes
  • 3 in conversation