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
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;
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;
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.
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;
@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.
Hi Art,
Thank you very much for this help.
Regards
Mirisage
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.