topic Re: proc compare between sql left join and proc transpose not getting the same result ? in New SAS User

proc compare between sql left join and proc transpose not getting the same result ?

ng1090 — Thu, 30 Sep 2021 02:48:25 GMT

data have;
length epoch $10. ;
input usubjid sstdtc sendtc epoch $ count;
informat sstdtc date9. sendtc date9.;
format sstdtc sendtc yymmdd10.;
datalines;
100 01JAN2010 15JAN2010 Screening 1
100 15JAN2010 15JUL2010 Treatment 2
100 16JUL2010 20DEC2010 Follow-up 3

;
run;

proc sort data = have out = have_sort;
by usubjid count;
run;

data have1;
input usubjid adtc ;
informat adtc date9.;
format adtc yymmdd10.;
datalines;
100 15JAN2010
100 14AUG2010
;
run;

proc sort data= have1 out = have1_sort;
by usubjid;
run;

Proc Sql;
Create Table want_sql as select a.usubjid, adtc , epoch,
count, sstdtc, sendtc From have1 as a left join have as b on a.usubjid= b.usubjid where
sstdtc <= adtc <=sendtc order by usubjid ,adtc,count;
Quit;

proc print data = want_sql;
title'proc sql result';
run;

/****proc transpose method******/

proc sql noprint;
create table _have as
select usubjid, epoch , sstdtc as start, sendtc as end ,count from have
order by usubjid, epoch,count;
quit;
proc sort data = _have out= have_s;
by usubjid count;
run;

proc transpose data=have_s out=s_t;
by usubjid count;
var epoch start end;

run;

proc transpose data=s_t out=set_f(drop=_name_) delimiter=_;
by usubjid;
id _name_ count;
var col1;
run;

data se_f;
merge s_t (in=ina) set_f (in=inb);
by usubjid;
drop col1;
run;

proc sort data = se_f ;
by usubjid ;
run;

data want;
merge have1 (in=ina) se_f(in=inf);
by usubjid;

if ina;
array sstdtc(3) start_1-start_3;
array sendtc(3) end_1-end_3;
array ep(3) epoch_1-epoch_3;

do i=1 to 3;
sestd=strip(sstdtc(i));
seend=strip(sendtc(i));
put adtc;

aestd=put(adtc,yymmdd10.);
format aestd yymmdd10.;
put sestd;
put aestd;
put seend;
put ep(i);

if sestd <= aestd <= seend then
do;
epoch = ep(i);
leave;
end;
end;
drop _name_ epoch_1 epoch_2 epoch_3 start_1 start_2 start_3 end_1 end_2 end_3 aestd i;

run;

data want_transpose;
set want;
rename sestd=sstdtc
seend = sendtc;
run;

proc sort data =want_transpose nodupkey ;
by usubjid count;
run;

proc print data=want_transpose;
title 'proc transpose result';
run;

proc compare base = want_sql compare= want_transpose;
run;

Re: proc compare between sql left join and proc transpose not getting the same result ?

Tom — Thu, 30 Sep 2021 03:08:43 GMT

Sounds like you are expecting the two different methods to produce the same output.

Do you know which one is right?

What is it you are trying to do?

Please describe the meaning of the two input datasets and the meaning of the result you are trying to create.

Re: proc compare between sql left join and proc transpose not getting the same result

ng1090 — Thu, 30 Sep 2021 03:17:04 GMT

Yes, I am trying to produce same output with 2 different approaches and
then comparing them using proc compare.
Output produced by proc Sql - left Join is correct, but using proc
transpose it is changing the data type in final output dataset.

While comparing it is displaying different data types for variables with
Date values. Proc Sql is giving as Date while proc transpose is giving
String data type and format.

Re: proc compare between sql left join and proc transpose not getting the same result

Tom — Thu, 30 Sep 2021 03:22:46 GMT

So the differences you see with PROC COMPARE is not the issue? Then why did you run PROC COMPARE?

Can you just demonstrate your issue with PROC TRANSPOSE without all of the other baggage?

PROC TRANSPOSE can take multiple variables and put them into a multiple observations of a single variable. If you transpose a mixed set of variables (at least one numeric and at least one character) then the numbers will HAVE to be converted into characters so that all of the values can be stored into the same target variable. A single variable can only have one type.

You still have not described what the input data IS nor what the output data means.

Re: proc compare between sql left join and proc transpose not getting the same result

ng1090 — Thu, 30 Sep 2021 03:40:40 GMT

Here are the input data sets

;
run;

proc sort data = have out = have_sort;
by usubjid count;
run;

data have1;
input usubjid adtc ;
informat adtc date9.;
format adtc yymmdd10.;
datalines;
100 15JAN2010
100 14AUG2010
;
run;

proc sort data= have1 out = have1_sort;
by usubjid;
run;

this is the proc SQL i'm using

proc print data = want_sql;
title'proc sql result';
run;

this is the result from proc SQL

I'm trying to get the same result with proc transpose.

Re: proc compare between sql left join and proc transpose not getting the same result ?

ballardw — Thu, 30 Sep 2021 03:56:05 GMT

There is pretty much no guarantee on the order of output from Proc SQL for tied values on an "order by" much less when you make no attempt to control order. With large data sets and some environments Proc SQL will return different order from the exact same input data with the exact same code.

Proc transpose will process values in order presented to the procedure but it may take some time to get used to the behavior.

Re: proc compare between sql left join and proc transpose not getting the same result

Kurt_Bremser — Thu, 30 Sep 2021 09:05:13 GMT

By including the character variable epoch in the VAR statement of PROC TRANSPOSE, you force the procedure to make COL1 character.

But the difference in code complexity between the single SQL step and the (in comparison) enormous transpose method alone should tell you which to use.