topic Re: Merging two macro results in SAS Studio

Merging two macro results

ak2011 — Thu, 04 Mar 2021 23:30:33 GMT

Hello,
I have two separate macro results (macro for janitor and macro for baker) and 
my aim is to merge the two datasets together, but they are not working.

Data set for janitor macro is jan and that for baker is bake.

Table 1 is janitor results and Table 2 is baker results (shown below).

proc print data=jan; doesn't work; same with proc print data=bake;
So to print janitor records, I just do proc print; and it works; same with baker.

Help is needed, please to merge dataset jan and dataset bake, and  to be merged 
finally with the original data Table o.

[I did not run the merge procedures as they would print the 17 records (small_t_data)

My data, code and results are found below:
Thanks in advance. [ No errors in log-too long so not shown]
ak.

[As usual, I have read the SAS community answers & SAS documentation on macros but still no solution
found]. I would appreciate your help, please.

/**MACRO*/

data full_tdata;
input id$ 1-6 job 7-8 jobyrin 9-11 jobyrout 18-20  icode$ 23-27  lung$ 29-35;
datalines;
OSa13	3	73	      78	  6191	 cacase
OSa30	1	39	      46	  7181	 cacase
OSa30	3	56	      64	  6191	 cacase
OSa73	1	23	      31	  7181	 popcon
OSa73	2	31	      42	  5130	 popcon
OSa86	3	46	      60	  6198	 cacon
OSa86	4	60	      70	  5130	 cacon
OSa93	3	68	      72	  6121	 popcon
OSf26	1	54	      54	  6198	 popcon
OSf26	2	70	      70	  6191	 popcon
OSh77	1	63	      66	  6121	 cacon
OSh77	2	66	      70	  6121	 cacon
OSh77	3	70	      71	  6121	 cacon
OSi84	1	67	      75	  6191	 cacase
OSi84	2	75	      81	  6191	 cacase
OSj17	3	60	      75	  6191	 cacase
OSj17	4	65	      70	  6191	 cacase
;

proc print data=full_tdata;
title 'Table O: Original full_tdata file'; run;

data small_tdata; set full_tdata; drop lung;


*max 4 jobs per subject;


%macro duration(database=small_tdata, icode=6191, Job_title=janitor);


*Creating a dataset containing all the jobs with selected icode;

data &Job_title;
set &database;
if icode = &icode;
run;


*transposing dataset so that the YEARIN and YEAROUT of all jobs held by a subject are in the same row;


*YEARIN;

proc transpose data = &Job_title out = &Job_title._yearin prefix = YEARIN;
by ID;
var jobyrin;
run;

proc sort data = &Job_title._yearin;
by id;
run;

*YEAROUT;

proc transpose data = &Job_title out = &Job_title._yearout prefix = YEAROUT;
by ID;
var jobyrout;
run;

proc sort data = &Job_title._yearout;
by id;
run;



data &Job_title._final;
merge &Job_title._yearin &Job_title._yearout;
by ID;

array YEARIN (3) YEARIN1 YEARIN2 YEARIN3;
array YEAROUT (3) YEAROUT1 YEAROUT2 YEAROUT3;

array YEARINv2 (3) YEARIN2 YEARIN3 YEARIN4 ;
array YEAROUTv2 (3) YEAROUT2 YEAROUT3 YEAROUT4;

array YEARIN_F (4) YEARIN1 YEARIN2 YEARIN3 YEARIN4;
array YEAROUT_F (4) YEAROUT1 YEAROUT2 YEAROUT3 YEAROUT4;

Array duration (4) Dur1 Dur2 Dur3 Dur4;



do i = 1 to 3;

if yearin(i) ne . and  yearinv2(i) ne . and yearinv2(i) le yearout(i) and yearoutv2(i) ge yearout(i) then do; yearinv2(i) = yearin(i); yearin(i) = .;yearout(i) = .;end;
else if  yearin(i) ne . and  yearinv2(i) ne . and yearinv2(i) le yearout(i) and yearoutv2(i) lt yearout(i) then do; yearinv2(i) = yearin(i);yearoutv2(i) = yearout(i); yearin(i) = .;yearout(i) = .;end;

end;

do i = 1 to 4;

if YEARIN_F(i) = . then  YEARIN_F(i) = 0;
if YEAROUT_F(i) = . then  YEAROUT_F(i) = 0;

duration(i) = YEAROUT_F(i) - YEARIN_F(i);
if YEARIN_F(i) ne 0 and YEAROUT_F(i) = YEARIN_F(i) then duration(i) = 0.5;
end;

&Job_title._dur = Dur1 + Dur2 + Dur3 + Dur4;

&Job_title._ever = 1;

keep ID &Job_title._dur &Job_title._ever;
run;

%MEND duration;


/* 1st macro-janitor icode 6191*/

data jan; set small_tdata;
 
%duration(database=small_tdata, icode=6191, Job_title=janitor);

proc print; 
title 'Table 1: janitor records';
run;
proc sort data=jan; by id;


/* 2nd macro-baker icode 7181*/

data bake; set small_tdata;
 
%duration(database=small_tdata,icode=7181, Job_title=baker);

proc print; 
title 'Table 2: baker records';
run;

Table O: Original full_tdata file


Obs	id	job	jobyrin	jobyrout	icode	lung
1	OSa13	3	73	78	6191	cacase
2	OSa30	1	39	46	7181	cacase
3	OSa30	3	56	64	6191	cacase
4	OSa73	1	23	31	7181	popcon
5	OSa73	2	31	42	5130	popcon
6	OSa86	3	46	60	6198	cacon
7	OSa86	4	60	70	5130	cacon
8	OSa93	3	68	72	6121	popcon
9	OSf26	1	54	54	6198	popcon
10	OSf26	2	70	70	6191	popcon
11	OSh77	1	63	66	6121	cacon
12	OSh77	2	66	70	6121	cacon
13	OSh77	3	70	71	6121	cacon
14	OSi84	1	67	75	6191	cacase
15	OSi84	2	75	81	6191	cacase
16	OSj17	3	60	75	6191	cacase
17	OSj17	4	65	70	6191	cacase

Table 1: janitor records


Obs	id	janitor_dur	janitor_ever
1	OSa13	5.0	1
2	OSa30	8.0	1
3	OSf26	0.5	1
4	OSi84	14.0	1
5	OSj17	15.0	1

Table 2: baker records


Obs	id	baker_dur		baker_ever
1	OSa30	7		1
2	OSa73	8		1


Tables 1 and 2 results are what I am expecting but the problem is how to merge them.
Thanks in advance for your help.

ak.

Re: Merging two macro results

ballardw — Thu, 04 Mar 2021 23:41:02 GMT

You do not provide any example of how the "merged" table is supposed to look.

I can "merge" those results just fine but the result is almost certainly not going to be what you expect or want.

It might not hurt to provide a little narrative describing what is need in the results. I have suspicion that you may be doing more manipulation than is actually needed but a clear of description is needed to know what a generic solution might be.

Hint: code that has lines 100 characters or more long needs to broken up/organized a bit. Having multiple statements on one line like this makes my head hurt. Plus it hides that you have nested do loops.

do i = 1 to 3;

if yearin(i) ne . and  yearinv2(i) ne . and yearinv2(i) le yearout(i) and yearoutv2(i) ge yearout(i) then do; yearinv2(i) = yearin(i); yearin(i) = .;yearout(i) = .;end;
else if  yearin(i) ne . and  yearinv2(i) ne . and yearinv2(i) le yearout(i) and yearoutv2(i) lt yearout(i) then do; yearinv2(i) = yearin(i);yearoutv2(i) = yearout(i); yearin(i) = .;yearout(i) = .;end;

end;

Re: Merging two macro results

Tom — Thu, 04 Mar 2021 23:41:37 GMT

So the only common variables between the two printouts at the end of your question is the first one. It is hard to tell from that print-out but it looks like it is named ID? So if you want to "merge" the datasets then use ID in the BY statement.

Your printout also does not show the NAME of those datasets, but it looks like they should be JANITOR_FINAL and BAKER_FINAL.

data want;
  merge janitor_final baker_final;
  by id;
run;

But only one of the values of ID appear in both datasets. So from most of the observations either the JANITOR... or BAKER... variables will be missing.

Re: Merging two macro results

Reeza — Thu, 04 Mar 2021 23:49:16 GMT

I would personally merge them to get this as the output:

ID TYPE duration EVER
OSa13 Janitor 5.0 1
OSa30 Janitor 8.0 1
...
OSa30 Baking 7 1
OSa73 Baking 8 1

Or are you envisioning something different?

Re: Merging two macro results

ak2011 — Fri, 05 Mar 2021 01:52:52 GMT

Actually, I am expecting something different.
I would post my expected output.
Thanks for your response.
ak.

Re: Merging two macro results

ak2011 — Fri, 05 Mar 2021 01:54:06 GMT

Actually,
My question is still unanswered. I would post the expected output in my next question.
Thank you.

ak.