SAS Array

[lancearmstrong1313]

Hello all,

I have a question about perhaps using an array. Below is an example of a dataset I have that records sales from 5 different cities on 3 different dates.

data test;
input city$ date date9. sales;
format date monyy7.;
cards;
A 01JAN2010 10
B 01JAN2010 11
C 01JAN2010 9
D 01JAN2010 8
E 01JAN2010 7

A 01FEB2010 12
B 01FEB2010 10
C 01FEB2010 7
D 01FEB2010 10
E 01FEB2010 9

A 01MAR2010 12
B 01MAR2010 11
C 01MAR2010 8
D 01MAR2010 11
E 01MAR2010 7
;
run;

Currently, information is stored in single columns. But what I want to do is to have each city be its own column with the sales under it. The code below does accomplish this:

data test1;
set test;
     if city="A" then A=sales;
     if city="B" then B=sales;
     if city="C" then C=sales;
     if city="D" then D=sales;
     if city="E" then E=sales;
keep date A--E;
run;

However, I'm curious if this can be accomplished using an array so that 1 row represents only 1 date. I know I could use several methods like PROC SQL for fix this (since as it stands each date appears on 5 rows:

[CODE]proc sql;
create table test2 as
select distinct date, sum(A) as A, sum(B) as B, sum(C) as C, sum(D) as D, sum(E) as E
from test1
group by date;
run; quit;[CODE]


but would like to know if an easier method (like an array) can be used so that each city is its own variable. Thanks for any help!

[Janus]

Try this:

data test2; set test;
by date;
if city="A" then A=sales;
if city="B" then B=sales;
if city="C" then C=sales;
if city="D" then D=sales;
if city="E" then E=sales;
retain;
if last.date then do;
	keep date A -- E;
	output;
end;
run;

[lancearmstrong1313]

Thanks. However, I'm still curious if there is a quicker way to automate it. For example, let's say I had data on 1,000 cities. It would be too tedious to right if city="A" then A=sales and do that 1,000 times. That's why I was not sure if an array was possible. I've used arrays quite a bit before but in this particular case I am working with character values and trying to create a new variable with the exact same name as the character value in the if city= statement and have had no luck with arrays in this particular case.

[Janus]

To me that calls for a macro function. If you create a macro variable with the list of cities and another with the number, you could easily have SAS scan the list and automate that section.