topic Re: Long to wide Array in SAS Programming

Long to wide Array

dsbihill — Wed, 14 May 2014 14:22:07 GMT

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

Re: Long to wide Array

Reeza — Wed, 14 May 2014 14:30:13 GMT

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;

Re: Long to wide Array

RW9 — Wed, 14 May 2014 14:30:38 GMT

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.

Re: Long to wide Array

data_null__ — Wed, 14 May 2014 14:33:02 GMT

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?

Re: Long to wide Array

dsbihill — Wed, 14 May 2014 14:57:20 GMT

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

Re: Long to wide Array

RW9 — Wed, 14 May 2014 15:16:54 GMT

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;

Re: Long to wide Array

ballardw — Wed, 14 May 2014 16:05:06 GMT

Do you need a dataset or a report with that layout?

Re: Long to wide Array

dsbihill — Wed, 14 May 2014 16:14:40 GMT

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;

Re: Long to wide Array

jwillis — Wed, 14 May 2014 16:47:02 GMT

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;

/* I have also seen this style */

if first.apg1 then do;

do z=1 to 5;

ytdcv(z) = 0;

end;

Re: Long to wide Array

ballardw — Wed, 14 May 2014 17:08:38 GMT

Heat map examples:

http://support.sas.com/kb/35/156.html

http://support.sas.com/kb/31/489.html

http://blogs.sas.com/content/iml/2013/12/11/visualize-a-matrix-in-sas-by-using-a-discrete-heat-map/