I am having some trouble with the following array data step. With my limited understanding of Arrays and SAS programing I thought that i could use an arrays variable name to locate data into the array. I cant seem to google anything that explains this very well. In this data step I am trying to use the Usage_type that is equal to the array variable names to change the data from long to wide. I get an error in the log for "Invalid numeric data, usage_type='AA'. Do i have to call out the usage type as a character some how? I know i could use proc transpose but i really need to start using arrays more. Can anyone tell me why this doesn't work.
Thank you so much.
Data temp_ytd_cost_v;
array ytdcv{4} US OH AA RM;
do until (last.apg1);
set temp_ytd_cost_v;
by customer gd apg1;
ytdcv{usage_type}=costV
end;
Run;
data type: usage_type = char, same as variable names of the array
I will need it in heat map type of report eventually (if that possible in sas) but my knowledge of sas has not made it there yet. Thanks to all the help here I just managed to get the following code to produce the output I am looking for minus any formatting. Any suggested improvements to this code or a report output would be welcome.
Thanks
data temp_YTD_Cost_V(drop=_utype costv trans_cost ytdaco usage_type);
array ytdcv{5} $ US OH AA RM Total;
do until (last.apg1);
Set temp_YTD_Cost_V;
by customer gd apg1;
if usage_type='US' then _utype=1;
if usage_type='OH' then _utype=2;
if usage_type='AA' then _utype=3;
if usage_type='RM' then _utype=4;
ytdcv{_utype}=costV;
end;
ytdcv{5}=sum(ytdcv{1},ytdcv{2},ytdcv{3},ytdcv{4});
run;
Try the following modification.
Data temp_ytd_cost_v;
array ytdcv{4} $ ( "US" "OH" "AA" "RM");
do until (last.apg1);
set temp_ytd_cost_v;
by customer gd apg1;
ytdcv{usage_type}=costV
end;
Run;
Your logic doesn't seem to make sense. The do loop is using last.apg1 as its condition, now last. is a per observation flag, the do loop is operating within an observation. Secondly why is the set statement within the do loop?
Please post some test data and how you would like it to look so I can se what you are trying to achieve.
In response to RW9's question
Data table in is listed below. The set statement causes me to iterate to a new observation every time it is read. This allows me to move through the records and populate the array without writing anything to my new record set until i am ready. Once i hit the last.apg1 i exit the loop and write the observation to my new record set
Data In
Customer | gd | APG1 | usage_type | CostV |
Z | X | A | AA | $600 |
Z | X | A | RM | $200 |
Z | X | A | US | $300 |
Z | X | B | AA | $5,000 |
Z | X | B | OH | $2,000 |
Z | X | B | RM | $1,200 |
Z | X | B | US | $400 |
Z | X | C | AA | $30 |
Z | X | C | RM | $50 |
Z | X | C | US | $60 |
Data out needed
Customer | GD | APG1 | AA | OH | RM | US |
Z | X | A | $600 | . | $200 | $300 |
Z | X | B | $5,000 | $2,000 | $1,200 | $400 |
Z | X | C | $30 | . | $50 | $60 |
TBH, I don't think I would use array processing for this. I realize you want to learn arrays and all, but this really is why there is a function transpose. There are also other methods to get to what you want:
data want;
merge have (keep=customer ge apg1 rename=(costv=aa) where=(usage_type="AA"))
have (keep=customer ge apg1 rename=(costv=oh) where=(usage_type="OH"))
have (keep=customer ge apg1 rename=(costv=rm) where=(usage_type="RM"))
have (keep=customer ge apg1 rename=(costv=us) where=(usage_type="US"));
by customer ge apg1;
run;
data want;
attrib aa oh rm us format=best.;
set have;
by apg1;
if usage_type="AA" then aa=costv;
if usage_type="OH" then OH=costv;
if usage_type="RM" then RM=costv;
if usage_type="US" then US=costv;
if last.apg1 then output;
run;
proc transpose data=have out=want;
by customer ge apg1;
var costv;
id usage_type;
idlabel usage_type;
run;
It doesn't work because an array index, the part between the {}, can't be a character.
You need to figure out a way to convert usage_type into a number that corresponds to variable location in the array.
Does USAGE_TYPE have the same value as the array element variable names you show US OH AA RM?
Do you need a dataset or a report with that layout?
I will need it in heat map type of report eventually (if that possible in sas) but my knowledge of sas has not made it there yet. Thanks to all the help here I just managed to get the following code to produce the output I am looking for minus any formatting. Any suggested improvements to this code or a report output would be welcome.
Thanks
data temp_YTD_Cost_V(drop=_utype costv trans_cost ytdaco usage_type);
array ytdcv{5} $ US OH AA RM Total;
do until (last.apg1);
Set temp_YTD_Cost_V;
by customer gd apg1;
if usage_type='US' then _utype=1;
if usage_type='OH' then _utype=2;
if usage_type='AA' then _utype=3;
if usage_type='RM' then _utype=4;
ytdcv{_utype}=costV;
end;
ytdcv{5}=sum(ytdcv{1},ytdcv{2},ytdcv{3},ytdcv{4});
run;
dsbihill,
Thank you for posting your code. I have not witnessed your approach before. I would recommend defaulting the array member values to zero. Some SAS functions fail when adding missing values.
/* I did not test this but I have seen this style */
array ytdcv{5} $ US OH AA RM Total 5*0;
or
/* I have also seen this style */
if first.apg1 then do;
do z=1 to 5;
ytdcv(z) = 0;
end;
end;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.