Create Variables with Do Loop

[NikosStratis]

Hello,

I am stuck with a small problem and I ask for your support.

I need to create a table with 60 columns, the value in each column is based on a date range.

I cannot get it to work.

I have the code

data work.data_have;

infile datalines delimiter=',';

input issue_date :date9. amount :eurox18.2;

datalines;

'13JAN2018'd,4546.77

'08MAR2018'd,117.39

'12JUL2018'd,130.40

'14JUL2018'd,1167.35

'16JUL2018'd,209.96

'17JUL2018'd,0.00

'12JAN2019'd,4854.69

'08MAR2019'd,117.39

'13JUL2019'd,1274.66

'15JUL2019'd,207.41

'26JUL2019'd,113.75

;

run;

data work.out_want;

set work.data_have;

array var(60) var_1 - var_60;

do i=1 to 60;

if (Issue_Date GE intnx('year', (today()- (i)), -1,'same')) and (Issue_Date LE (TODAY()- (i)) ) = i

then var(i) = Amount;

format var_1 - var_60 eurox18.2;

end;

drop i;

run;

%let var_i = var_1,var_2,var_3,var_4,var_5,var_6,var_7,var_8,var_9,var_10,

var_11,var_12,var_13,var_14,var_15,var_16,var_17,var_18,var_19,var_20,

var_21,var_22,var_23,var_24,var_25,var_26,var_27,var_28,var_29,var_30,

var_31,var_32,var_33,var_34,var_35,var_36,var_37,var_38,var_39,var_40,

var_41,var_42,var_43,var_44,var_45,var_46,var_47,var_48,var_49,var_50,

var_51,var_52,var_53,var_54,var_55,var_56,var_57,var_58,var_59,var_60;

;

The Output that I need is shown below:

Everytime the date range select falls in certain {i} it should fill the sum(amount) in the appropriate column.

var_1	var_2	var_3	var_4	var_5	var_6	var_7	var_8	var_9	var_10	var_11	var_12
6567,90 €	6567,90 €	6567,90 €	6567,90 €	6567,90 €	6567,90 €	6567,90 €	6567,90 €	6567,90 €	6567,90 €	4.854,69 €	4.854,69 €

Thank you in advance!

[rudfaden]

It is not reallu clear to me what your problem is. The code seems to work fine.

[NikosStratis]

Hello!

Thank you for your quick response!

The code is working fine, but it does not do what I need... My logic must be wrong...

I need to check every time the difference in Issue Date vs current date - x time and for every match, I need to fill the appropriate column.

like it shows below.

var_1	var_2	var_3	var_4	var_5	var_6	var_7	var_8	var_9	var_10	var_11	var_12
6567,90 €	6567,90 €	6567,90 €	6567,90 €	6567,90 €	6567,90 €	6567,90 €	6567,90 €	6567,90 €	6567,90 €	4.854,69 €	4.854,69 €

[andreas_lds]

Please explain the expected result and the difference to what you get right now.

I won't open any office-files, so you have to add the relevant information to your post or wait for somebody willing to open the attached docx.

[NikosStratis]

Hello!

this is what i need to get:

var_1      var_2         var_3         var_4         var_5         var_6           var_7         var_8
6567,90  € 6567,90  € 6567,90  € 6567,90   € 6567,90   € 6567,90  € 6567,90  € 6567,90 €

Instead I get values only in column var_1...

Every time the date difference matches, I need to put the sum(amount) in the appropriate column var_1.. or 2 or 3 ...

[rudfaden]

Then you need to change your if statement. It does exactly what you told it to do.

[NikosStratis]

Thanks! I knew I had a problem with the logic... can you send me a short helper please?

[rudfaden]

You need to describe what you want the condition to be.

[NikosStratis]

OK,

I need to check the Issue Date through the code below:

if (Issue_Date GE intnx('year', (today()- (i)), -1,'same')) and (Issue_Date LE (TODAY()- (i)) ) = i

Every time it meets the conditions it should write the amount in the corresponding var_ column.

I need to check it for 60 days, each date separately.

in the example data the amount should be the same for days 1 - 12 (example) so it should write the same amount in columns var_1 through var_12.

[rudfaden · Accepted solution]

I can see that you put an i at the end of the statement.

if (Issue_Date GE intnx('year', (today()- (i)), -1,'same')) and (Issue_Date LE (TODAY()- (i)) ) = i

Is that a mistake?

Maybe you want this

data work.out_want;
set work.data_have;
array var(60) var_1 - var_60;
do i=1 to 60;
if (Issue_Date GE intnx('year', (today()- (i)), -1,'same')) and (Issue_Date LE (TODAY()- (i)) ) 
then var(i) = Amount;
format var_1 - var_60 eurox18.2;
end;
drop i;
run;

[NikosStratis]

YES!

This is what I want!

Thank you very much!!!