BookmarkSubscribeRSS Feed
exRhodesian
Calcite | Level 5
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
6 REPLIES 6
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
SASJedi
SAS Super FREQ
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;
Check out my Jedi SAS Tricks for SAS Users
DBailey
Lapis Lazuli | Level 10
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;
Ksharp
Super User
[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
exRhodesian
Calcite | Level 5
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
exRhodesian
Calcite | Level 5
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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1084 views
  • 0 likes
  • 5 in conversation