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
Use PROC REPORT with ODS EXCEL. REPORT will print group values only once for each group.
Use PROC REPORT with ODS EXCEL. REPORT will print group values only once for each group.
Of course! Genius!
Thanks
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.