topic Re: How could I read these date variables using proc SQL in SAS Procedures

How could I read these date variables using proc SQL

Mirisage — Mon, 30 Jul 2012 19:17:40 GMT

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

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

art297 — Mon, 30 Jul 2012 19:31:26 GMT

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;

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

tish — Mon, 30 Jul 2012 19:35:35 GMT

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.

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

Mirisage — Tue, 31 Jul 2012 03:12:52 GMT

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;

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

art297 — Tue, 31 Jul 2012 04:16:07 GMT

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

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

Mirisage — Tue, 31 Jul 2012 16:50:23 GMT

Hi Art,

Thank you very much for this help.

Regards

Mirisage