DATA Step, Macro, Functions and more

PROC SQL sub or nested queries..

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL sub or nested queries..

Hi all,
Happy Friday and with that I have a brain teaser that hopefully can be solved!

I have a database in which one column that is numerically filled correlates to values in another column so that for example when:

cd_number=1 cd_value=lastname (jones, smith, etc etc)
cd_number=2 cd_value=firstname (Larry, Steven)
cd_number=45 cd_value=wound_class (1,2)

etc etc

so my question is,
when I write my sql query I want it so that:
select cd_value as lastname (when cd_number=1), cd_value as firstname (when cd_number=2), cd_value as wound_class, etc etc

I have tried different iterations including select x, (select a.cd_value from x.table when when cd_number=1) as lastname, b, c from ...

However it does creates an error if I attempt it, this way.
Any suggestions?

Thank you.

Lawrence Block
Valued Guide
Posts: 2,191

Re: PROC SQL sub or nested queries..

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?

Super User
Posts: 10,770

Re: PROC SQL sub or nested queries..

Although,I do not understand what your mean .The best way is to give your original data and what do you want data to look like.
As far as i understand.

select cd_number, case when cd_number=1 then cd_value end as lastname,
case when cd_number=2 then cd_value end as firstname,

Respected Advisor
Posts: 4,736

Re: PROC SQL sub or nested queries..

Hi Lawrence

What you're looking for is a case expression, i.e:

when cd_number=1 then lastname
when cd_number=2 then firstname
when cd_number=45 then wound_class
else ' '
end as value format=$100.

Valued Guide
Posts: 2,191

Re: PROC SQL sub or nested queries..

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...
Regular Contributor
Regular Contributor
Posts: 170

Re: PROC SQL sub or nested queries..

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 333
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 333
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 333
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...

Thanks again.
Ask a Question
Discussion stats
  • 5 replies
  • 4 in conversation