Merge and aggregate datasets: Difference between revisions
→{{header|REXX}}: Add SAS task
(→{{header|Prolog}}: Better allow for "nan") |
(→{{header|REXX}}: Add SAS task) |
||
Line 1,361:
| 4004 | Wirth | 2020-11-05 | 15.4 | 7.7 |
| 5005 | Kurtz | | | |</pre>
=={{header|SAS}}==
<lang sas> %let datefmt=E8601DA10.;
data patient;
infile "patient.csv" dsd dlm=',';
attrib
id length=4
lastname length=$10;
input id lastname;
data visit;
infile "visit.csv" dsd dlm=',';
attrib
id length=4
date informat=&datefmt format=&datefmt
score length=8;
input id date score;
proc sql;
create view temp as
select * from visit natural join patient;
select * from
(select id, max(date) format=&datefmt as max_date from temp group by id)
natural full join
(select id, sum(score) as sum_score, avg(score) as avg_score from temp group by id);</lang>
{{out}}
<pre>: id sum_score avg_score max_date
: ------------------------------------------
: 1001 17.4 5.8 2020-11-19
: 2002 6.8 6.8 2020-10-08
: 3003 . . 2020-11-12
: 4004 15.4 7.7 2020-11-05</pre>
=={{header|SPSS}}==
| |||