Combining tables and displaying first row from the first table while keeping all records

ammarhm — Wed, 16 Mar 2022 06:05:41 GMT

Hi everyone,

suppose we have two tables that w are combining to one:

DATA table_1;
input id censor gender $ ;
DATALINES;
1 1 M 
2 0 F 
3 1 F 
;
run;


DATA Table_2;
input id treatment R1 R2 R3 response $ outcome $;
DATALINES;
1 1  40 44 20 No B
1 1  45 46 16 Yes A
1 1  40 32 20 No B
1 1  47 32 23 No B
2 0  41 25 22 No B
2 1  54 35 13 No B
2 1  48 50 9 Yes A
2 1  36 33 12 Yes B
3 0  49 51 8 Yes A
3 1  49 52 10 Yes A
3 1  44 35 12 No A
3 1  49 50 8 Yes A

;
run;


proc sql;
create table combined as
select
a.*,
b.treatment,
b.R1,
b.R2,
b.R3,
b.response,
b.outcome
from table_1 a left join table_2 b
on a.id=b.id
;
quit;

this will generate the following outcome:

id	censor	gender	treatment	R1	R2	R3	response	outcome
1	1	M	1	40	32	20	No	B
1	1	M	1	47	32	23	No	B
1	1	M	1	40	44	20	No	B
1	1	M	1	45	46	16	Yes	A
2	1	F	1	48	50	9	Yes	A
2	1	F	1	54	35	13	No	B
2	1	F	0	41	25	22	No	B
2	1	F	1	36	33	12	Yes	B
3	1	F	1	49	50	8	Yes	A
3	1	F	1	44	35	12	No	A
3	1	F	1	49	52	10	Yes	A
3	1	F	0	49	51	8	Yes	A

This is of course great for data manipulation, but say i wanted to generate an output like this to put into an excel and give it to someone:

id	censor	gender	treatment	R1	R2	R3	response	outcome
1	1	M	1	40	32	20	No	B
			1	47	32	23	No	B
			1	40	44	20	No	B
			1	45	46	16	Yes	A
2	1	F	1	48	50	9	Yes	A
			1	54	35	13	No	B
			0	41	25	22	No	B
			1	36	33	12	Yes	B
3	1	F	1	49	50	8	Yes	A
			1	44	35	12	No	A
			1	49	52	10	Yes	A
			0	49	51	8	Yes	A

So basically data from table 1 appears only once and does not repeat itself. This is just to improve the visibility of the data, and the this table is not going to be used for analysis.

Does anyone know how to generate this outcome?

Thanks

Re: Combining tables and displaying first row from the first table while keeping all records

Kurt_Bremser — Wed, 16 Mar 2022 06:37:22 GMT

Use PROC REPORT with ODS EXCEL. REPORT will print group values only once for each group.

Re: Combining tables and displaying first row from the first table while keeping all records

ammarhm — Wed, 16 Mar 2022 06:43:26 GMT

Of course! Genius!

Thanks

topic Combining tables and displaying first row from the first table while keeping all records in SAS Programming

Combining tables and displaying first row from the first table while keeping all records

Re: Combining tables and displaying first row from the first table while keeping all records

Re: Combining tables and displaying first row from the first table while keeping all records