the sql query concept is selecting information from streams of data which you coordinate or corelate in the join with where a clause restricting what is in each stream.
So for example you have to join the a.value as firstname with b.value as lastname where their keys are the same except A has the CD=2 and B has the CD=1.
Does that help?
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 ;
, 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...
Sorry for the delay in response I have been swamped!
So the table structure looks like that below:
Surg_ID cd_field cd_value "field name" format in table end format
1 100 Block Last name char char
1 414 1000 Surg_start character time:HH:MM
1 415 1215 Surg_end character time:HH:MM
2 100 Smith Last name char char
2 414 900 Surg_start character time:HH:MM
2 415 1147 Surg_end character time:HH:MM
3 100 Jones Last name char char
3 414 1600 Surg_start character time:HH:MM
3 415 1753 Surg_end character time:HH:MM
where the first three columns are in the table for example. So in essence, yes I am transposing data as each surgery may have 90 entries or so.
So at the end of the day, each line would have distinct values so that:
surg_ID, cd_value as name where cd_field=100, cd_value as surg_start where cd_field=414 etc etc..
I can do this currently in a macro by pulling out the cd_fields I want, putting them in a table, writing a macro that will pull the values and then another quick macro to merge all the values. However, I am trying to write this query in SQL w/o macros so it can be ported later to a stored procedure in SQL...