How to set values in variable from list of values? How to retain values in data step?

[Easybeat]

Hi!

I’ve got table with date variable last_of_month and set of values for it.

And I’ve got a macro which do some calculations on each date from last_of_month, making new table with stats on each date.

Now I need to do summary calculations in total on the whole period of time in order to draw simple histogram with dates on X axis and values of sum on Y axis.

The code:

%Macro pos;
data _null_;
set dates end=eof;
call symputx(cats('obs_date',_N_),last_of_month);
if eof then call symputx('numdates',_n_);
run;

data summary;
length sum 9;
format date datetime20.;
format sum nlnum32.;
retain _all_;
run;

%do i=1 %to &numdates.;

%let date_= %sysfunc(dhms(&&obs_date&I,0,0,0));

proq sql;
…code making several tables full_trades_&&obs_date&i…
Quit;

Proc means data=full_trades_&&obs_date&i sum noprint;
Var VALrur;
Output out=summary_&&obs_date&i sum=sum;
Run;

Proc append base=summary data=summary_&&obs_date&i force;
Where _type_=0;
Run;

Data summary;
Set summary;
Date=&date_;
Retain _all_;
Run;

The problem is that the result in summary is following:

sum	date
.	28feb
100	28feb
50	28feb

So I need to a) eliminate the first row in order to draw competent histogram and b) to retain the values from last_of_month variable (now it takes only the last value from the list and put it in every row).

Or maybe there's an easy way to put values for sum and date from last_of_month and summary stats from proc means?

[PaigeMiller]

Show us (a portion of) the data set DATES.

Why are you doing this via macro? Using a BY statement or a CLASS statement in PROC MEANS would seem to eliminate the need to do all of this macro looping.

[Easybeat]

I do the macro because values for last_of_month in DATES table are dynamic. User enters the time range (BeginDate and EndDate), last_of_months values then generated, then sql and data step codes build new data on each date from last_of_month.

I'm not sure that BY, CLASS and either NWAY options can help in such case. Or please advise me on that. The full code is below in chat.

[Easybeat]

I do the macro because values for last_of_month in DATES table are dynamic. User enters the time range (BeginDate and EndDate), last_of_months values then generated, then sql and data step codes build new data on each date from last_of_month.

I'm not sure that BY, CLASS and either NWAY options can help in such case. Or please advise me on that. The full code is below in chat.

[Reeza]

---

@Easybeat wrote:

I do the macro because values for last_of_month in DATES table are dynamic. User enters the time range (BeginDate and EndDate), last_of_months values then generated, then sql and data step codes build new data on each date from last_of_month.

I'm not sure that BY, CLASS and either NWAY options can help in such case. Or please advise me on that. The full code is below in chat.

---

Yes, it can help in this case. No data was posted.

[Reeza]

This should be a single PROC MEANS statement with a BY/CLASS statement, not a macro.
Look at the NWAY option on the PROC MEANS statement and the WAYS & TYPES statements within PROC MEANS that allow you to control the different levels of aggregations.

where date=intnx('month', date, 0, 'e') is a little trick to filter out the last day of the month for your records...you could create an indicator variable to help with the different levels of aggregation.

[Tom]

You will get a better answer if you provide working example data.

Is there some reason why you are storing dates as datetime values (number of seconds) instead of date values (number of days)?  You seem to be setting the timepart to zero.   Does your actual data have non-zero time of day parts in the "date" variable?

[ballardw]

It will be extremely helpful if you start to differentiate "date" and "datetime" when referring to SAS variables. One is days, the other seconds and you need to consider different parameters with most functions.

You should include that SQL code "making several tables". It might actually be the problem in this process.

[Easybeat]

So this is the code for the whole program:

options fullstimer;

%let INN = 0000000000;
%let BeginDate = 01jan2019;
%let EndDate = 01feb2019;
%let Months = %sysfunc(intck(month,"&BeginDate."d,"&EndDate."d));

data dates;
do m = 0 to &months.;
first_of_month = intnx('month',"&BeginDate."d,m,'s');
last_of_month = intnx('month', "&BeginDate."d,m,'e');
output;
end;
format first_of_month last_of_month date9.;
run;

PROC SQL;


%Macro pos;

data _null_;
set dates end=eof;
call symputx(cats('obs_date',_N_),last_of_month);
if eof then call symputx('numdates',_n_);
run;

data summary;
length sum 8;
format date datetime20.;
format sum nlnum32.;
retain _all_;
run;

%do i=1 %to &numdates.;
%let date_= %sysfunc(dhms(&&obs_date&i,0,0,0));  

proc sql;

CREATE TABLE WORK.OPEN_POS_&&obs_date&i AS 
   SELECT 
 t9.BOARD_ID, 
 t9.NAME, 
t1.BUY_SELL, 
   t1.ST_ACTUAL, 
          t1.TRADE_DATE, 
          t1.TYP, 
          t1.STATUS, 
          t1.CONFIRMED, 
          t3.SHORT_NAME AS SHORT_NAME3 length=30 format=$30. informat=$30.,
          t3.ISIN length=12 format=$12. informat=$12.,
          t2.SHORT_NAME length=30 format=$30. informat=$30.,
          t2.INN length=10 format=$10. informat=$10.,
          t6.SHORT_NAME AS SHORT_NAME1 length=30 format=$30. informat=$30.,
          t6.INN AS INN1 length=10 format=$10. informat=$10.,
          t1.PRICE, 
          t1.VAL, 
          t1.AMOUNT,
		  t1.DUE_DATE,
          t1.CLIENT_CODE_ID,
          t4.DETAILS ,
		t7.CodeCurSimple length=4 format=$4. informat=$4.,
		t8.RATE, 
          	t8.UNIT
      FROM SV_EQ_TRADES t1
           LEFT JOIN SV_EQ_FIRMS t2 ON (t1.FIRM_ID = t2.FIRM_ID)
           LEFT JOIN SV_EQ_SECS t3 ON (t1.SECURITY_ID = t3.SECURITY_ID)
           LEFT JOIN SV_EQ_CLIENTCODES t4 ON (t1.CLIENT_CODE_ID = t4.CLIENT_CODE_ID)
           LEFT JOIN SV_EQ_FIRMS t6 ON (t1.CP_FIRM_ID = t6.FIRM_ID)
		LEFT JOIN WORK.CODESEQTOCURRENCY t7 ON (t1.CURRENCY_ID = t7.CodeEqCurr)
		LEFT JOIN WORK.FILTER_FOR_SV_CURR_XRATES t8 ON t7.CodeCurSimple = t8.CURRENCY1_ID and t1.TRADE_DATE = t8.X_DATE
		   LEFT JOIN SV_EQ_BOARDS t9 ON (t1.BOARD_ID = t9.BOARD_ID)
      WHERE (t1.TRADE_DATE<=&date_ and t1.DUE_DATE>=&date_ and t1.TRADE_DATE<t1.DUE_DATE) AND t2.INN = "&INN" 
AND t1.TYP in ('r','h','j') 
and t1.STATUS = 'M'
and t1.CONFIRMED = 'C'
and t1.ST_ACTUAL = 'A'
ORDER BY t1.TRADE_DATE;

quit;


data full_trades_&&obs_date&i;
set work.OPEN_POS_&&obs_date&i;
length Deal_Type $7;
length Details_Type $7;
length repo_type $14;

if TYP in ("N" "T") then
		Deal_Type= "Market";
	else if TYP in ("R" "r" "H" "h" "J" "j") then
		Deal_Type= "Repo";
if DETAILS = '' then Details_Type="Self";
else if DETAILS ne '' then Details_Type="Client";

if typ in ('R' 'H' 'J') and buy_sell = 'S' then
		repo_type='straight_repo';
else if typ in ('r' 'h' 'j') and buy_sell = 'B' then
		repo_type='straight_repo';
else if typ in ('R' 'H' 'J') and buy_sell = 'B' then
		repo_type='reverse_repo';
else if typ in ('r' 'h' 'j') and buy_sell = 'S' then
		repo_type='reverse_repo';

Month=trade_date;
format month dtmonyy.;

run;

title1 "Данные на %sysfunc(putn(%sysfunc(dhms(&&obs_date&i,0,0,0)),datetime20.))" ; 

proc tabulate data=full_trades_&&obs_date&i;
	class Buy_Sell CodeCurSimple name repo_type/ order=freq ;
	var VALrur VAL;

	
	table CodeCurSimple   all={s=[background=graydf ]},
		(repo_type)*(VALrur*(N*f=nlnum32. sum*f=nlnum32.2 colpctsum)VAL*(sum*f=nlnum32.2)) all*VALrur*(N*f=nlnum32. sum*f=nlnum32.2 colpctsum*f=nlnum32.2);
		
	run;
title1;


proc means data=full_trades_&&obs_date&i sum noprint;
	
	var VALrur;
	output out=summary_&&obs_date&i sum=sum;
	format sum nlnum32.;
run;


proc append base=summary
			data=summary_&&obs_date&i force;
				where _type_=0;
				
			run;

data summary;
set summary;
date = &date_;
retain date;
run;

%_eg_conditional_dropds(WORK.OPEN_POS_&&obs_date&i);
%end;

%mend pos;

%pos

[Kurt_Bremser]

From your WHERE:

WHERE (t1.TRADE_DATE<=&date_ and t1.DUE_DATE>=&date_ and t1.TRADE_DATE<t1.DUE_DATE)

I take it that your "observation periods" are not mutually exclusive. A single observation from the source dataset can appear in multiple summaries.

Is that intended? 

[Easybeat]

Yes, it is intended. I need to see the "position" (sum of liabilities) on each date. It's like balance sheet on the end of each month.

[Kurt_Bremser · Accepted solution]

Then I would do this the other way round. First, for every transaction, create an observation for every end-of-month it crosses, with a "period" date as "index". Then use this index as the top-level class in your calculations.

[Easybeat]

Actually, the program do necessary calculations and show me the "position" (sum of liabilities) on each date in proc tabulate part of the code.

But I need to draw a histogram and, thus, all the sums need to be in one table (or not?).