Merging two separate macro results

ak2011 — Fri, 05 Mar 2021 04:38:31 GMT

Hello,
In my previous post,I mentioned that I wanted to merge 2 macro results datasets :Table 1 (janitor)
and Table 2 (Baker). Janitor dataset is called jan and baker is bake.
The merged file mjb produced unexpected results (Table 3,not shown-Is the small_tdata set from which
the macro was developed(17 observations)
I have still not found the solution to
my problem inspite of reading extensively on the subject from SAS Forum answers, SAS online documentation
and so on.
I would appreciate your help,please.

My code, data and results are found below. My expected output is placed after results.
Thanks in advance. [The log has no errors but too long so not shown].
ak.

/**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;


/* 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;


/*Merging the janitor (Table 1) and baker(Table 2) files*/

proc sort data=jan; by id;

proc sort data=bake; by id;

data mjb; merge  jan bake; by id; run;


proc print data=mjb;
Title 'Table 3: Merged Table 1(janitor) and Table 2 (baker)-Unexpected merge';
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


1	OSa30	7
2	OSa73	8

Expected output:

id janitor_dur janitor_ever baker_dur baker_ever
osa13 5 1 0 0
osa30 8 1 7 1
osa73 0 0 8 1
osf26 0.5 1 0 0
osi84 14 1 0 0
osj17 15 1 0 0

Tables 1 and 2 are ok, but the problem is merging them to obtain the expected output above.

I would finally merge janitor and baker files with table 0: original file.

Please help. Thanks.

ak.

Re: Merging two separate macro results

Reeza — Fri, 05 Mar 2021 04:39:18 GMT

The solution posted in your previous question, marked as correct, is the correct solution then.

If it doesn't work, please explain how otherwise you're just going to get the same answer again and again.

Re: Merging two separate macro results

ak2011 — Fri, 05 Mar 2021 22:56:49 GMT

Actually, the solution marked as correct was not the solution, I clicked accept as solution by mistake. Sorry.
Fact is I am merging Tables 1 and 2 above to produce the expected output shown. I used the right merging code; proc sort by id for janitor and baker each, but I obtained 17 records(not shown). Thank you.

Re: Merging two separate macro results

Reeza — Sat, 06 Mar 2021 00:49:24 GMT

How is the solution provided. Please be explicit.

topic Re: Merging two separate macro results in SAS Studio

Merging two separate macro results

Re: Merging two separate macro results

Re: Merging two separate macro results

Re: Merging two separate macro results