topic Re: Data concatenation with proc sql in SAS Procedures

Data concatenation with proc sql

deleted_user — Tue, 01 Feb 2011 18:29:55 GMT

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.

Re: Data concatenation with proc sql

CurtisMack — Tue, 01 Feb 2011 18:53:23 GMT

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;

Re: Data concatenation with proc sql

CurtisMack — Tue, 01 Feb 2011 18:56:03 GMT

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;

Re: Data concatenation with proc sql

deleted_user — Tue, 01 Feb 2011 19:17:51 GMT

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