SQL Join Macro

[Ody]

Hello all,

Looking for some direction here on a post I made a few days ago.

This code is working as intended. I am creating several child tables (child1, child2, child3, etc...) from the dataset test.

data test;
infile datalines dsd truncover;
input 
ordernum:$3.
order:1.
code:$3.
flex:$3.	
;

datalines;
111, 1, qwe, 12a
111, 2, gfd, 52f
111, 3, sdf, 00c
222, 1, erw, 58h
222, 2, fgh, 77i
333, 1, saa, 36j
333, 2, ath, 49d
333, 3, gdw, 20a
444, 1, etg, 46c
444, 2, hef, 58r
444, 3, sfy, 86c
444, 4, hrl, 22m
555, 1, mmj, 76l
555, 2, bhg, 13a
555, 3, dgy, 66z
555, 4, ggd, 17x
555, 5, dss, 59r
;;;

data parent;
infile datalines dsd truncover;
input 
ordernum:$3.;

datalines;
111
222
333
444
555;


proc sql noprint;
select max(Order) into : order_num
from test;
quit;
%put Max  Count = &order_num;


options mlogic mprint;
%macro split;

data %do i = 1 %to &order_num;
child&i %end;;

set test;
select(Order);
%do i = 1 %to &order_num;
when ("&i") output child&i; 
%end;
otherwise;
end;
run;

%mend split;
%split;

I want to join those child tables to the parent table and add fields from them to the parent. The fields in the child tables all have the same name.

Here is what I've written so far. I know it doesnt work and at this point I'm pretty much stuck. I dont really know how I should write the syntax and my google-fu has reached its limits. Appreciate any help. 

%macro joinAll;
proc sql;
create table chk as
select 
a.*,

%if (&i. = &order_num.) %then %do
b.code as code&i.,
b.flex as flex&i.
%end

from parent a

%if (&i. = &order_num.) %then %do
left join child&i. b on a.ordernum = b.ordernum
%end;

quit;
%mend joinAll;

%joinall;

[Kurt_Bremser]

From where do you get the macro variable i in the macro joinall?

[Ody]

Ah, you're right. I didn't define i = 1.

[Astounding]

This approach is taking a medium-difficult problem and turning it into a horribly complex nightmare.  I would suggest you start over, starting with:

proc transpose data=test out=codes prefix=code (keep=ordernum code: );

by ordernum;

var code;

run;

proc transpose data=test out=flexes prefix=flex (keep=ordernum flex: );

by ordernum;

var flex;

run;

Take a look at the results at that point ... they should be easy to join your parent table.

[Ody]

Thanks for the response.

I need to separate a large data set into smaller chunks by the order field and then merge certain fields from those data sets back to the parent table. On the parent table the ordernum is a unique key.

This is a small example I'm trying to understand so that I can apply it to a much larger dataset. The number of orders is an unknown variable, could be 3, could be 8, etc, which means I will have an unknown number of datasets. That's why didnt use proc transpose.

Joining tables is not a problem, it's doing it via macro and selecting the same field from those tables thats giving me a problem

[Astounding]

OK, I'll try not to jump to conclusions here.  Why not take the sample data that you have already posted, and illustrate the result you would like to obtain?

[Ody]

I would expect the macro result to look like the result from this query. I'm likely way off base with my macro attempt...

proc sql;
create table chk as
select 
a.*,
b.code as code1,
b.flex as flex1,
c.code as code2,
c.flex as flex2,
d.code as code3,
d.flex as flex3,
e.code as code4,
e.flex as flex4,
f.code as code5,
f.flex as flex5

from parent a 
left join child1 b on a.ordernum = b.ordernum
left join child2 c on a.ordernum = c.ordernum
left join child3 d on a.ordernum = d.ordernum
left join child4 e on a.ordernum = e.ordernum
left join child5 f on a.ordernum = f.ordernum

;
quit;

[Astounding]

This looks like exactly what you would get if you transposed the data and joined.  PROC TRANSPOSE creates as many variables as are needed without you needing to know ahead of time what that number will be.

[Ody]

Sorry, I guess I misunderstood what you were trying to do with proc transpose.

Could you provide an example of the merge for me using this example data?

[Astounding · Accepted solution]

After the two PROC TRANSPOSE steps above, the merge would look like this:

data want;

merge codes (in=in1) flexes (in=in2) parent (in=in3);

by ordernum;

run;

If you want all the records, regardless of mismatches, that's the program.  If you want to select based on matches/mismatches with the parent data set, you can use the in= variables to do that.

[Ody]

SAS didnt like the (keep=) part of your proc transpose so I removed it and ran the three statements.

The result looks good. It's funny how complicated we try to make things sometimes.

I'll mark this as an accepted solution and see how well this works with my large datasets. Appreciate the time and effort, thank you!