BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

 

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

3 REPLIES 3
art297
Opal | Level 21

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

 

knveraraju91
Barite | Level 11

Thank you.

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

art297
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 622 views
  • 1 like
  • 2 in conversation