SAS QUERY

[rakeshvvv]

I have dataset with variables ID, VISITNUM, DATE, DAYS

I should write a query in such a way that for every ID, the difference in days should not be more then 49 between two visits.

100020001           1              2014-12-04          43

100020001           2              2015-01-15          85

100020002           1              2015-03-18          43

100020002           2              2015-04-29          85

100030002           1              2014-11-03          43

100030002           2              2014-12-22          92

100030002           3              2015-02-09          141

100030002           4              2015-03-23          183

100030003           1              2015-05-04          41

140010004           1              2015-02-11          28

140010004           2              2015-03-27          82

140020001           1              2014-09-22          43

140020001           2              2014-11-02          84

140020001           3              2014-12-21          133

140020001           4              2015-02-09          183

140020001           5              2015-03-23          225

My output should look like,

ID                          NEWVARIABLE

140020001,        2015-02-09; 2014-12-21;DIFF=50

140010004     2015-03-27; 2015-02-11;DIFF=54

Thanks everyone for great help.

Rakesh

[RW9]

Hi,

There are many posts on this type of subject on here, for example:

[Ksharp · Accepted solution]

data have;
input ID : $20. VISITNUM DATE : yymmdd10. DAYS ;
format      DATE  yymmdd10.;
cards;
100020001           1              2014-12-04          43
100020001           2              2015-01-15          85
100020002           1              2015-03-18          43
100020002           2              2015-04-29          85
100030002           1              2014-11-03          43
100030002           2              2014-12-22          92
100030002           3              2015-02-09          141
100030002           4              2015-03-23          183
100030003           1              2015-05-04          41
140010004           1              2015-02-11          28
140010004           2              2015-03-27          82
140020001           1              2014-09-22          43
140020001           2              2014-11-02          84
140020001           3              2014-12-21          133
140020001           4              2015-02-09          183
140020001           5              2015-03-23          225
;
run;
data want;
 set have;
 length new $ 40;
 lag_date=lag(date);
 dif=dif(DAYS);     
 if id=lag(id) and dif gt 49 then do; new=cats(vvalue(lag_date),';',vvalue(date),';'); output;end;
 keep id new;
 format lag_date yymmdd10.;
 run;
 

Xia Keshan