topic Re: Create Variables with Do Loop in SAS Programming

Create Variables with Do Loop

NikosStratis — Tue, 01 Oct 2019 08:32:28 GMT

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!

Re: Create Variables with Do Loop

rudfaden — Tue, 01 Oct 2019 09:07:17 GMT

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

Re: Create Variables with Do Loop

andreas_lds — Tue, 01 Oct 2019 09:23:59 GMT

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.

Re: Create Variables with Do Loop

NikosStratis — Tue, 01 Oct 2019 09:24:40 GMT

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 €

Re: Create Variables with Do Loop

NikosStratis — Tue, 01 Oct 2019 09:28:27 GMT

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

Re: Create Variables with Do Loop

rudfaden — Tue, 01 Oct 2019 09:57:10 GMT

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

Re: Create Variables with Do Loop

NikosStratis — Tue, 01 Oct 2019 10:03:17 GMT

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

Re: Create Variables with Do Loop

rudfaden — Tue, 01 Oct 2019 10:09:15 GMT

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

Re: Create Variables with Do Loop

NikosStratis — Tue, 01 Oct 2019 10:20:52 GMT

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.

Re: Create Variables with Do Loop

rudfaden — Tue, 01 Oct 2019 10:54:30 GMT

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;

Re: Create Variables with Do Loop

NikosStratis — Tue, 01 Oct 2019 11:02:36 GMT

YES!

This is what I want!

Thank you very much!!!