PROBLEM - CANT GET A DESIRED OUTPUT - DO LOOP

[Abhiraj]

Hi

I have a small problem which I tried to solve but got stuck.

I created the following data set - 

data car;
infile datalines dsd;
input brand $ loan ;
datalines;
mini,70
,30
,20
,10
toyota,80
,40
;
run;

f

There are two variables - brand and loan.

what i need to do is in first observation of each brand, loan variable should show only outstanding loan amount.

for example - for brand mini in first observation, output should be - mini       70-(30+20+10) = 10, and rest all observations remaining same.

EXPECTED FULL OUTPUT

brand       loan

mini         10

                30

                20

                10

Toyota    40

               40

I NEED TO DO THIS USING DO LOOP.

[PeterClemmensen]

Hi and welcome to the SAS Communities

Please show us your code so far? 🙂

[Abhiraj]

data final;
do i = 1 to 2;
set car nobs=k;
if brand eq '' then total+loan;
if brand ne '' then total = 0;
output;
end;
run;

Tried this. but it is wrong i know!!

[ballardw]

---

@Abhiraj wrote:

Hi

I have a small problem which I tried to solve but got stuck.

I created the following data set - 

data car;
infile datalines dsd;
input brand $ loan ;
datalines;
mini,70
,30
,20
,10
toyota,80
,40
;
run;

f

There are two variables - brand and loan.

what i need to do is in first observation of each brand, loan variable should show only outstanding loan amount.

for example - for brand mini in first observation, output should be - mini       70-(30+20+10) = 10, and rest all observations remaining same.

EXPECTED FULL OUTPUT

brand       loan

mini         10

                30

                20

                10

Toyota    40

               40

I NEED TO DO THIS USING DO LOOP.

---

How do we know what the initial value of the loan is? Outstanding loan amount?

You should include all identification variables in EVERY record. You create unneeded complications by not having "mini" or "Toyota" on all of the records related to each.

Conceptually an initial value of a loan and a loan payment are different things and likely should be different variables as any thing that changes the order of your data means that you are likely not to be able to identify the "initial value".

[novinosrin]

Hi @Abhiraj  Sure one of us will provide the solution. However may i ask why the weird data structure with blank values for by groups. I am increasingly concerned to have such a structure in a production environment or to have a meaningful analysis for the matter. 

[Abhiraj]

hi

this is the assignment given to me. I know data is weird but cant help with that

[novinosrin]

Ok Thanks @Abhiraj 

Try-->

data car;
infile datalines dsd;
input brand $ loan ;
datalines;
mini,70
,30
,20
,10
toyota,80
,40
;
run;

data want;
set car;
if brand>' ' then do;
do i=_n_+1 to nobs  ;
set car(rename=(brand=_brand loan=_loan)) nobs=nobs point=i;
if brand ne _brand and _brand>' ' then leave;
_s=sum(_s,_loan);
end;
loan=loan-_s;
end;
drop _:;
run;

[Abhiraj]

Thanks @novinosrin .. It works.. I couldnt understand what multiple set statements do in a single data step...

[novinosrin]

Basically in this instance it does a Self join/merge one to many to satisfy your conditions to compute sum. There are so many articles online you can find about the use of multiple set statements in a datastep for joins/merges/look ups etc. That's how I learned. Have fun!

[Ksharp]

data car;
infile datalines dsd;
input brand $ loan ;
datalines;
mini,70
,30
,20
,10
toyota,80
,40
;
run;
data have;
 set car;
 if not missing(brand) then group+1;
run;
data want;
sum=0;
 do until(last.group);
  set have;
  by group;
  if not first.group then sum+loan;
 end;

 do until(last.group);
  set have;
  by group;
  if first.group then loan=loan-sum;
  output;
 end;
 drop sum group;
 run;