topic Re: PROBLEM - CANT GET A DESIRED OUTPUT - DO LOOP in SAS Programming

PROBLEM - CANT GET A DESIRED OUTPUT - DO LOOP

Abhiraj — Wed, 24 Apr 2019 14:45:47 GMT

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;

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

Toyota 40

I NEED TO DO THIS USING DO LOOP.

Re: PROBLEM - CANT GET A DESIRED OUTPUT - DO LOOP

PeterClemmensen — Wed, 24 Apr 2019 15:29:08 GMT

Hi and welcome to the SAS Communities

Please show us your code so far? 🙂

Re: PROBLEM - CANT GET A DESIRED OUTPUT - DO LOOP

Abhiraj — Wed, 24 Apr 2019 15:37:19 GMT

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!!

Re: PROBLEM - CANT GET A DESIRED OUTPUT - DO LOOP

ballardw — Wed, 24 Apr 2019 15:44:25 GMT

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

Re: PROBLEM - CANT GET A DESIRED OUTPUT - DO LOOP

novinosrin — Wed, 24 Apr 2019 15:46:03 GMT

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.

Re: PROBLEM - CANT GET A DESIRED OUTPUT - DO LOOP

Abhiraj — Wed, 24 Apr 2019 15:51:54 GMT

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

Re: PROBLEM - CANT GET A DESIRED OUTPUT - DO LOOP

novinosrin — Wed, 24 Apr 2019 16:21:24 GMT

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;

Re: PROBLEM - CANT GET A DESIRED OUTPUT - DO LOOP

Abhiraj — Thu, 25 Apr 2019 07:55:02 GMT

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

Re: PROBLEM - CANT GET A DESIRED OUTPUT - DO LOOP

novinosrin — Thu, 25 Apr 2019 12:52:03 GMT

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!

Re: PROBLEM - CANT GET A DESIRED OUTPUT - DO LOOP

Ksharp — Thu, 25 Apr 2019 13:56:53 GMT

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;