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.
Hi and welcome to the SAS Communities
Please show us your code so far? 🙂
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!!
@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".
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.
hi
this is the assignment given to me. I know data is weird but cant help with that
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;
Thanks @novinosrin .. It works.. I couldnt understand what multiple set statements do in a single data step...
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!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: