Data concatenation with proc sql

[deleted_user]

Hi: How to imitate the below data steps using proc sql:

data two;
set one (keep = subjid trt)
output;
trt = 99 /*** assuming trt values in one are 1 and 2 ***/
output;
run;

Thanks.

[CurtisMack]

I don't think your psuedo code does what you think it does so I ran it to show the real results. Here it is with the SQL equivalent.

data one;
subjid = "Sub1";
trt = 1; output;
trt = 2; output;
subjid = "Sub2";
trt = 1; output;
trt = 2; output;
run;

data two;
set one (keep = subjid trt);
output;
trt = 99;
output;
run;

proc sql;
create table tow2 as
select subjid, trt
from one
union all
select subjid,99 as trt
from one;
quit;

[CurtisMack]

This seems more likely to be what you were trying to do.


data two;
set one (keep = subjid trt);
by subjid;
output;
if last.subjid then do;
trt = 99;
output;
end;
run;

proc sql;
create table tow2 as
select subjid, trt
from one
union all
select subjid,99 as trt
from (select distinct subjid from one);
quit;

[deleted_user]

Thanks Curtis. Dataset 'One' is already unique [one row per subjid]. Sorry, I left this info out. But you got it anyway 😉