Hello SAS Users,
I need to create a dynamic variable by adding prefix into an array element. I have a SAS dataset and i need to create a running total for each variables. However, i keep getting an error related to array ref doesn't exist. I don't want to hard code the variable names as new variable names will be added into the dataset every month.
here is the SAS code i'm running
data have;
input id FAS_1990_01 FAS_1991_01 FAS_1991_02 FAS_1991_03 FAS_1992_01 FAS_1992_02 FAS_1992_03 FAS_1993_01;
datalines;
1 7983 1000 1200 3099 7083 1009 1900 7099
2 9000 1500 1300 4390 7983 1080 1280 3199
3 3898 3090 1400 4980 7973 1050 1260 3299
4 4654 2483 1500 3879 7953 1080 1400 3899
;
run;
data need;
set have;
by id;
array deal_sum{*} FAS:;
do i = 1 to dim(total);
total_||deal_suml{i} + deal_sum{i};
end;
run;
Can you store your data in a long form instead of wide? This violates the best practice of not having 'data' in your column headers.
Its also easier to add rows to a dataset versus adding columns. For the running total, if you have SAS/ETS look at PROC EXPAND.
Given your example dataset, what do you want the resulting dataset to look like?
Art, CEO, AnalystFinder.com
This will creat the desired output.
data need;
set have;
by id;
total_FAS_1990_01 + FAS_1990_01;
total_FAS_1991_01 + FAS_1991_01;
total_FAS_1991_02 + FAS_1991_02;
total_FAS_1991_03 + FAS_1991_03;
total_FAS_1992_01 + FAS_1992_01;
total_FAS_1992_02 + FAS_1992_02;
total_FAS_1992_03 + FAS_1992_03;
total_FAS_1993_01 + FAS_1993_01;
run;
Your code assumes you have an array named TOTAL. You can create one, but you would need to identify the variables in it. Since you have but a few, it might be simplest to hard-code the names:
data need;
set have;
by id;
array deal_sum{*} FAS:;
array total {*} total_FAS_1990_01 total_FAS_1991_01 total_FAS_1991_02 total_FAS_1991_03
total_FAS_1992_01 total_FAS_1992_02 total_FAS_1992_03 total_FAS_1993_01;
do i = 1 to dim(total);
total{i} + deal_sum{i};
end;
run;
If your actual data set contains more variables, you can use macro language to construct the set of names. But for 8 variable names, why complicate the program?
Can you store your data in a long form instead of wide? This violates the best practice of not having 'data' in your column headers.
Its also easier to add rows to a dataset versus adding columns. For the running total, if you have SAS/ETS look at PROC EXPAND.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.