topic Re: Help in Proc sql code in SAS Programming

Help in Proc sql code

knveraraju91 — Sun, 06 May 2018 23:54:33 GMT

data one;
input id trt$ date $18.;
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
;
PROC SQL;
CREATE TABLE TWO AS
SELECT id,trt,input(date,is8601dt.) as exstdt format datetime18.,((max(datepart(exstdt))-min(datepart(exstdt)))+1) /7 as aval,count(id) as ava,
group by id,trt
                
from one;
QUIT;

Dear,

I am trying to do some calculations in my data. I think I can do by datasteps, but I want to try proc sql. Please help. Thank you

rules;

Calculate by id,trt

param= 'numberofrecords" count number of obs by id and trt
param="duration in weeks" last date-first date+1 converted to weeks by id,trt
param="compliance" numberofrecords/{round(durationinweeks/3)+1}

I did not populate the aval values for param="duration in weeks" and "compliance" as I do not know what values I get from the code.

output needed:

id trt param aval
1 A numberofrecords 4
1 A durartioninweeks
1 A compliance
1 B numberofrecords 4
1 B durartioninweeks
1 B compliance
2 A numberofrecords 4
2 A durartioninweeks
2 A compliance
2 B numberofrecords 4
2 B durartioninweeks
2 B compliance

Re: Help in Proc sql code

art297 — Mon, 07 May 2018 01:19:46 GMT

Your code was close. Here are a couple of needed changes:

PROC SQL;
  CREATE TABLE TWO AS
    SELECT id,trt,
           input(date,is8601dt.) as exstdt format datetime18.,
           ((max(datepart(calculated exstdt))-
             min(datepart(calculated exstdt)))+1) /7 as aval,
           count(id) as ava
      from one
        group by id,trt         
  ;
QUIT;

Art, CEO, AnalystFinder.com

Re: Help in Proc sql code

knveraraju91 — Mon, 07 May 2018 02:04:54 GMT

Thank you.

How to write code for param values for each calculated aval values.

Re: Help in Proc sql code

art297 — Mon, 07 May 2018 03:22:41 GMT

You'll have to check the following code to insure that the calculations are those which you actually need:

PROC SQL;
  CREATE TABLE TWO AS
    SELECT id,trt,
           input(date,is8601dt.) as exstdt format datetime18.,
           ((max(datepart(calculated exstdt))-
             min(datepart(calculated exstdt)))+1) /7 as aval,
           count(id) as ava,
           intck('week',min(datepart(calculated exstdt)),
              max(datepart(calculated exstdt)),'c') as duration,
           calculated ava/(round(calculated duration/3)+1) as compliance
      from one
        group by id,trt         
  ;
QUIT;

Art, CEO, AnalystFinder.com