BookmarkSubscribeRSS Feed
Siddhartha
Calcite | Level 5
I have four columns named prd_grp,prd-subgroup,prd_desc and loan_amount.
The data is as follows:


prd_grp prd_subgroup prd_desc loan_amount

ASSETS All_Other Loans 234
ASSETS All_Other abc 345
ASSETS All_Other Quoted_Shares 213
LIABILIITES Deposits Current_Deposs 435
LIABILIITES Deposits Fixed 325
LIABILIITES Deposits NIDs & Repos 256
LIABILIITES Deposits Savings 218

Below is the output I needed

Item Loan_amount

ASSETS
All_Other
Loans 234
abc 345
Quoted_Shares Held 213
Total 692
LIABILIITES
Deposits
Current Deposits 435
Fixed 325
NIDs & Repos 256
Savings 218
Grand_Total 1234

Can anyone help me on this, as this is critical.
Thanks in advance.

Regards,
Siddhartha
3 REPLIES 3
ArtC
Rhodochrosite | Level 12
You did not specify how you wanted your results (data or report), so here are a couple of possible solutions.
[pre]data balance;
length prd_grp $11 prd_subgroup $10 prd_desc $15;
input prd_grp $ prd_subgroup $ prd_desc $ loan_amount;
datalines;
ASSETS All_Other Loans 234
ASSETS All_Other abc 345
ASSETS All_Other Quoted_Shares 213
LIABILIITES Deposits Current_Deposs 435
LIABILIITES Deposits Fixed 325
LIABILIITES Deposits NIDs&Repos 256
LIABILIITES Deposits Savings 218
run;
title 'balance sheet';
proc summary data=balance ;
class prd_grp prd_desc ;
var loan_amount;
output out=sumry(where=(_type_ ne 1)) sum=total;
run;
proc print data=sumry;
run;

proc report data=balance nowd;
column prd_grp prd_desc loan_amount;
define prd_grp / group;
define prd_desc / group;
define loan_amount / analysis sum;
break after prd_grp / summarize;
rbreak after / summarize;
run;[/pre]
Your example output did not really show what you wanted to do with prd_subgroup, but adding it into one of these solutions is very straightforward.
SPR
Quartz | Level 8 SPR
Quartz | Level 8
Hello Suddhartha,

This is another solution (pure dataset operations):
[pre]
data i;
length prd_grp prd_subgroup prd_desc $15;
input prd_grp $ prd_subgroup $ prd_desc $ loan_amount;
datalines;
ASSETS All_Other Loans 234
ASSETS All_Other abc 345
ASSETS All_Other Quoted_Shares 213
LIABILIITES Deposits Current_Deposs 435
LIABILIITES Deposits Fixed 325
LIABILIITES Deposits NIDs_&_Repos 256
LIABILIITES Deposits Savings 218
;
run;
data t;
retain prd_grp prd_subgroup prd_desc loan_amount total grand_total pr;
set i end=ie;
if _n_=1 then grand_total=0;
if first.prd_grp then do; pr=1; output; end;
if first.prd_subgroup then do; pr=2; output; total=0; end;
pr=3; output;
total+loan_amount;
if last.prd_subgroup then do; pr=4; output; end;
grand_total+loan_amount;
by prd_grp prd_subgroup;
keep prd_grp prd_subgroup prd_desc loan_amount total grand_total pr;
if ie then do; pr=5; output; end;
run;
data r(keep=Item Loan_Amount);
retain Item Loan_Amount;
set t;
if pr=1 then do; Item=prd_grp; loan_amount=.; end;
if pr=2 then do; Item=prd_subgroup; loan_amount=.; end;
if pr=3 then do; Item=prd_desc; end;
if pr=4 then do; Item="Total"; loan_amount=Total; end;
if pr=5 then do; Item="Grand Total"; loan_amount=grand_Total; end;
run;
[/pre]
Sincerely,
SPR
Cynthia_sas
SAS Super FREQ
Hi:
You already posted this question in the ODS forum and several answers have been posted there, too.

cynthia

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1021 views
  • 0 likes
  • 4 in conversation