Hi, By "to get the output i need without additional transpose and data step", I assumed you expected to get the output within two steps of Data Step and/or PROC SQL. Basically, to achieve the result similar to that of PROC TRANSPOSE, the statement UNION is recommended in PROC SQL. Please see the example program below: data one;
input id trt $ date ymddttm16.;
format date datetime18.;
datalines;
1 A 2014-04-26T13:10
1 A 2014-05-17T11:30
1 A 2014-06-07T11:45
1 A 2014-06-28T13:30
1 B 2014-04-26T13:10
1 B 2014-05-17T11:30
1 B 2014-06-07T11:45
1 B 2014-06-28T13:30
2 A 2014-04-26T13:10
2 A 2014-05-17T11:30
2 A 2014-06-07T11:45
2 A 2014-06-28T13:30
2 B 2014-04-26T13:10
2 B 2014-05-17T11:30
2 B 2014-06-07T11:45
2 B 2014-06-28T13:30
;
run;
proc sql;
create table two as
select distinct id, trt, 'numberofrecords' as param length=32, 1 as paramn,
count(*) as aval
from one
group by id, trt
union
select distinct id, trt, 'durationinweeks', 2,
intck('week', min(datepart(date)), max(datepart(date)), 'c')
from one
group by id, trt
select distinct id, trt, 'compliance', 3,
count(*)/(round(intck('week', min(datepart(date)), max(datepart(date)), 'c')/3)+1)
from one
group by id, trt
order by id, trt, paramn;
quit; There is, of course, an obvious downside in this two-step programming, which is redundancy, e.g. the GROUP BY statement; and the CALCULATED statement would be no longer applicable since "number of records" and "duration in weeks" were not defined in the same SELECT statement any more. In addition, if the definition of variable PARAMN was skipped, observations in data set TWO would be sorted by ID, TRT, and PARAM alphabetically. To avoid such redundancy, I'm afraid a third step of PROC TRANSPOSE is necessary. You can apply the NAME= option, along with the data set option RENAME= for the DATA= option in PROC TRANSPOSE statement, something similar to the following, so that a fourth Data Step can be skipped: proc transpose out=three( rename=(col1=aval)) name=param;
var numberofrecords durationinweeks compliance;
by id trt;
quit; To make sure COL2, COL3, and COL4 will not be generated, it is essential to apply SELECT DISTINCT, instead of SELECT statement in PROC SQL. P.S. I took the liberty to modify the programming of Data Step ONE so that variable DATE is defined as numeric directly. Hope this would Help!
... View more