Hi Everyone,
This is what I have
Date isin dummy price r_stock oib month
2000-03-28 FI0001001234 1 70 0.060 0.67 3
2000-03-29 FI0001001234 1 71 0.102 1 3
2000-03-30 FI0001001234 1 69 -0.052 -1 3
2000-03-31 FI0001001234 1 68 -0.050 0.33 3
2000-04-01 FI0001001234 1 69 0.070 1 4
I want this
Date isin dummy price r_stock oib month
2000-03-31 FI0001001234 1 68 0.015 1 3
Here,
Date is the last day of the month
Isin is the isin for the company (I have over 200 different isin)
Dummy is the dummy for the given isin (it could be zero for other isin)
Price is the price on the last day of the month
r_stock is the average r_stock of the month
oib is the total sum of the month
month is the relevant month
I have some missing values in the dataset. All missing values are in the "." form.
Can someone help? Thanks.
/* UNTESTED CODE */
data have2;
set have;
if date = intnx('month',date,0,'e') then last_day=1;
else last_day=0;
format date mmdd4.;
run;
proc summary data=have2;
class date isin;
id dummy month;
var r_stock oib;
var price/weight=last_day;
output out=want sum(oib)=sum_oib mean(r_stock)=mean_r_stock
sum(price)=price_on_last_day_of_month;
run;
/* UNTESTED CODE */
data have2;
set have;
if date = intnx('month',date,0,'e') then last_day=1;
else last_day=0;
format date mmdd4.;
run;
proc summary data=have2;
class date isin;
id dummy month;
var r_stock oib;
var price/weight=last_day;
output out=want sum(oib)=sum_oib mean(r_stock)=mean_r_stock
sum(price)=price_on_last_day_of_month;
run;
Can be solved with a data-step and retained variables.
data want; set have; by isin date; length lastPrice avgStock sumStock sumOIB numObs 8; retain lastPrice sumStock sumOIB numObs; if first.date then do; /* set variables in retain to 0 */ end; numObs = numObs +1; sumStock = sum(sumStock, r_stock); ... if last.date then do; avgStock = sumStock / numObs; output; end; keep Date isin dummy lastPrice sumStock sumOIB month; rename lastPrice = price ....; run;
Post test-data as data-step using datalines if you want tested code.
@andreas_lds wrote:
Can be solved with a data-step and retained variables.
data want; set have; by isin date; length lastPrice avgStock sumStock sumOIB numObs 8; retain lastPrice sumStock sumOIB numObs; if first.date then do; /* set variables in retain to 0 */ end; numObs = numObs +1; sumStock = sum(sumStock, r_stock); ... if last.date then do; avgStock = sumStock / numObs; output; end; keep Date isin dummy lastPrice sumStock sumOIB month; rename lastPrice = price ....; run;
It does not appear to me that this produces results by month, it appears to me that this gives results over the entire data set.
@PaigeMiller: my fault .... you are right ... still no usable test-data, so i won't suggest anything 😐
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 25. 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.