Proc Compare Result-between Proc SQL and Proc Transpose not matching, getting different results Help

ng1090 — Wed, 29 Sep 2021 17:56:20 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 Result-between Proc SQL and Proc Transpose not matching, getting different results

Sajid01 — Sat, 02 Oct 2021 12:31:43 GMT

Hello
What are you trying to achieve?

The first appears to be comprehensible but not your second series steps.

Re: Proc Compare Result-between Proc SQL and Proc Transpose not matching, getting different results

ng1090 — Sat, 02 Oct 2021 12:40:04 GMT

Hi
I already got the solution thanks for your response. Yes, I made some
changes to the second step. That was dummy data I just wanted to check I
will get the same result with both methods (proc SQL and proc transpose )or
not.

Thanks

Re: Proc Compare Result-between Proc SQL and Proc Transpose not matching, getting different results

Sajid01 — Sat, 02 Oct 2021 12:56:04 GMT

Great.
If you can share your observation on the forum that may help others.

topic Re: Proc Compare Result-between Proc SQL and Proc Transpose not matching, getting different results in SAS Programming

Proc Compare Result-between Proc SQL and Proc Transpose not matching, getting different results Help

Re: Proc Compare Result-between Proc SQL and Proc Transpose not matching, getting different results

Re: Proc Compare Result-between Proc SQL and Proc Transpose not matching, getting different results

Re: Proc Compare Result-between Proc SQL and Proc Transpose not matching, getting different results