BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ody
Quartz | Level 8 Ody
Quartz | Level 8

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;

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

10 REPLIES 10
Ody
Quartz | Level 8 Ody
Quartz | Level 8
Ah, you're right. I didn't define i = 1.
Astounding
PROC Star

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
Quartz | Level 8 Ody
Quartz | Level 8
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
PROC Star

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
Quartz | Level 8 Ody
Quartz | Level 8

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
PROC Star

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
Quartz | Level 8 Ody
Quartz | Level 8
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
PROC Star

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
Quartz | Level 8 Ody
Quartz | Level 8
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!

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
  • 10 replies
  • 2991 views
  • 0 likes
  • 3 in conversation