~LB
Subject to assumptions:
1 each row relates to one person keyed with column "ID"
2 each person has only one firstName and only one LastName
3 where a person has more than one wound_class, you want a separate row for each wound_class each row having the unique ID, firstname and lastName
the approach I had in mind (join streams together) would look like :[pre]proc sql ;
select a.ID
, b.cd_value as firstName
, c.cd_value as lastName
, z.cd_value as wound_class
from ( select distinct ID from your_data_set) as A
join your_data_set as B
on a.ID = b.ID
join your_data_set as C
on a.ID = c.ID
join your_data_set as Z
on a.ID = Z.ID
where b.cd_number = 2
& c.cd_number = 1
& z.cd_number = 45
;[/pre] This works just fine for a small number of columns, but your "wound_class" hints at 45 !
Although you could move the relation between cd_number and data name into something like metadata, I think the generated code (dynamically transposing the data into columns named in another table) would bump into one of the old limitations of PROC SQL, that it was unable to support more than 16 or 32 input tables.
When processing 45 separate tables, the error message reports[pre]ERROR: Query is too complex to be processed. It references more than 16 tables.[/pre]
Despite this message, I have had some success beyond 16 tables so, with a little macro I cooked up earlier (%genPatN repeats a string pattern with an increasing number), this seems to work up to an "sql_limit=39"
However, inside the sql select statement, I see no way to create a dynamic re-name (as new_column_name, controlled by metadata) yet , so more later
Unless you (~LB) have only a few columns or values for cd_number ... please explain...