topic Create table using arrays from data on 2 other tables in SAS Programming

Create table using arrays from data on 2 other tables

exRhodesian — Fri, 06 May 2011 12:50:56 GMT

Hi,
Table-X contains people’s names and scores.( sorted via Name )
Table-Y is a copy of Table-X but with unique names.(sorted via Name)
Table-Z is a copy of Table-Y with the individual scores from Table-X appearing next to the relevant Name. Maximum scores stored = 20.
- How would one create Table-Z ? Would one use arrays for the max 20 occurance of scores ?
Thanks in Advance.

Table-X
A 1
A 3
A 7
B 2
F 2
F 9
K 1
K 8
P 2
P 8
P 9

Table-Y
A
B
F
K
P

Table-Z
A 1 3 7
B 2
F 2 9
K 1 8
P 2 8 9

Re: Create table using arrays from data on 2 other tables

SPR — Fri, 06 May 2011 13:45:48 GMT

Hello exRhodesian,

This is a solution:
[pre]
data X;
input name $ score;
datalines;
A 1
A 3
A 7
B 2
F 2
F 9
K 1
K 8
P 2
P 8
P 9
run;
proc sort data=X;
by name;
run;
proc transpose data=X out=Z (drop=_:) prefix=s_;
var score;
by name;
run;
[/pre]
Sincerely,
SPR

Re: Create table using arrays from data on 2 other tables

SASJedi — Sun, 08 May 2011 01:30:25 GMT

Give this a whirl:

data Narrow;
input name $ scr;
datalines;
A 1
A 3
A 7
B 2
F 2
F 9
K 1
K 8
P 2
P 8
P 9
run;
proc sort data=Narrow;
by name;
run;
data Wide;
set Narrow;
by Name;
array Score{20};
retain score:;
if First.Name then do;
call missing (of Score{*});
_index=0;
end;
_index+1;
Score{_index}=scr;
if Last.Name then output;
drop _index scr;
run;

Re: Create table using arrays from data on 2 other tables

DBailey — Sun, 08 May 2011 04:04:44 GMT

Another way would be to write a text file that you could read later. I don't think you'll need table Y for this approach:

data _null_;
set x;
by name;
file z /*not sure of the syntax to set lreclen*/;
if first.name then put name, score @;
else put score @;
if last.name then put;

Re: Create table using arrays from data on 2 other tables

Ksharp — Mon, 09 May 2011 01:04:23 GMT

[pre]
data X;
input name $ score;
datalines;
A 1
A 3
A 7
B 2
F 2
F 9
K 1
K 8
P 2
P 8
P 9
run;
data want(drop=score);
set x;
by name;
length scores $ 50;
retain scores;
if first.name then call missing (scores);
scores=catx(' ',scores,score);
if last.name then output;
run;
[/pre]

Ksharp

Re: Create table using arrays from data on 2 other tables

exRhodesian — Mon, 09 May 2011 08:36:46 GMT

Hi, many thanks for your assistance. After trying them out , yours, SPR, seemed to work spot on.
These are actually for Towns/Suburbs here in South Africa with Postal codes where,eg. there are 3 towns in SA with the name of Newtown. I figured it much better to create a table as seen below rather than have a huge long table where Newtown would appear 3 times. Some names appear 11 times. Thanks once again,
Adrian from Johannesburg.

Code used
:
proc sort data=LOCAL.BOX;
by TOWN BOX;
run;
proc transpose data=LOCAL.BOX out=COMBINE (drop=_:) prefix=s_;
var BOX;
by TOWN;
run;

NEW BRIGHTON.......6200 7070
NEWCAVE............... 2380 6255 9400 9944 9950
NEWCLARE............. 2112
NEWLANDS............. 0049 5201 7725
NEWTON................. 4380 8420
NEWTON PARK........6055
NEWTOWN...............2113 4310 4400

Re: Create table using arrays from data on 2 other tables

exRhodesian — Mon, 09 May 2011 10:01:48 GMT

Hi SASJedi - tried out yours and it also works spot on as seen below. Thanks once again.
Adrian in Johannesburg

proc sort data=LOCAL.BOX;
by TOWN;
run;
data BOX_COMBINED;
set LOCAL.BOX;
by TOWN;
array BOXES{20};
retain BOX:;
if First.TOWN then do;
call missing (of BOXES{*});
_index=0;
end;
_index+1;
BOXES{_index}=BOX;
if Last.TOWN then output;
drop _index BOX;
run;