Hey everyone,
I have been trying to determine the amount of days between the first date and the last date. I have tried subtracting the first date from the last date but have run into a strange issue. If the amount of days between the first and last date is over a 100, it is still listed as 100. So if there was 200 days between the first and last visit, it would be listed as 100.
Example:
PROC SQL;
CREATE TABLE FIRSTLAST AS
SELECT ID,
COUNT(ID) AS COUNT,
MIN(DOS_FROM) AS FIRST,
MAX(DOS_TO) AS LAST,
DOS_TO - DOS_FROM AS DAYS
FROM DATA
GROUP BY ID;
QUIT;
Any help would be much appreciated and if anyone has a better suggestion as to how to construct a better syntax, I am all ears.
Thanks,
Mike
Like this?
data HAVE;
do ID = 1 to 5;
do N=1 to 5;
DOS_FROM=today()-365;
DOS_TO=DOS_FROM+int(ranuni(0)*365);
output;
end;
end;
format DOS: date9.;
run;
proc sql;
create table WANT as
select ID
,count(ID) as COUNT
,min(DOS_FROM) as FIRST format=date9.
,max(DOS_TO) as LAST format=date9.
,max(DOS_TO)-min(DOS_FROM) as DAYS
from HAVE
group by ID;
quit;
ID | COUNT | FIRST | LAST | DAYS |
---|---|---|---|---|
1 | 5 | 09SEP2015 | 18JUN2016 | 283 |
2 | 5 | 09SEP2015 | 07SEP2016 | 364 |
3 | 5 | 09SEP2015 | 20AUG2016 | 346 |
4 | 5 | 09SEP2015 | 17AUG2016 | 343 |
5 | 5 | 09SEP2015 | 26AUG2016 | 352 |
Like this?
data HAVE;
do ID = 1 to 5;
do N=1 to 5;
DOS_FROM=today()-365;
DOS_TO=DOS_FROM+int(ranuni(0)*365);
output;
end;
end;
format DOS: date9.;
run;
proc sql;
create table WANT as
select ID
,count(ID) as COUNT
,min(DOS_FROM) as FIRST format=date9.
,max(DOS_TO) as LAST format=date9.
,max(DOS_TO)-min(DOS_FROM) as DAYS
from HAVE
group by ID;
quit;
ID | COUNT | FIRST | LAST | DAYS |
---|---|---|---|---|
1 | 5 | 09SEP2015 | 18JUN2016 | 283 |
2 | 5 | 09SEP2015 | 07SEP2016 | 364 |
3 | 5 | 09SEP2015 | 20AUG2016 | 346 |
4 | 5 | 09SEP2015 | 17AUG2016 | 343 |
5 | 5 | 09SEP2015 | 26AUG2016 | 352 |
Thank you so much Chris! You are a lifesaver.
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.
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.