Fluorite | Level 6

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

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;
Quit;

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

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

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.

12 REPLIES 12
Diamond | Level 26

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

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.

--
Paige Miller
Fluorite | Level 6

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

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.
Fluorite | Level 6

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

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.
Super User

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

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

Super User

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

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.
Super User

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

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?

Super User

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

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.

Fluorite | Level 6

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

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.ST_ACTUAL,
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
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)
AND t1.TYP in ('r','h','j')
and t1.STATUS = 'M'
and t1.CONFIRMED = 'C'
and t1.ST_ACTUAL = 'A'

quit;

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';

format month dtmonyy.;

run;

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

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;

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

``````
Super User

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

``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?

Fluorite | Level 6

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

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.

Super User

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

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.

Fluorite | Level 6

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

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?).

Discussion stats
• 12 replies
• 829 views
• 2 likes
• 6 in conversation