i have a below dataset
data temp;
input cust dt :date9.;
format dt date9.;
datalines;
1 01apr2021
1 02apr2021
1 10apr2021
2 15apr2021
2 18apr2021
2 20apr2021
2 25apr2021
3 29apr2021
3 03may2021
4 04may2021
;
run;
and i want a single entry for each cusotmer with fpd and lpd transactions
ex: output should be
cust fpd lpd
1 01apr2021 10apr2021
2 15apr2021 25apr2021
3 29apr2021 03may2021
4 04may2021 04may2021
i already have sql query for the same i want the solution in data step. Thanks in advance!!
sql query :
proc sql;
select cust,min(dt) as fpd format date9.,max(dt) as lpd format date9. from temp
group by cust;
quit;
Assuming the data has been sorted .
data temp;
input cust dt :date9.;
format dt date9.;
datalines;
1 01apr2021
1 02apr2021
1 10apr2021
2 15apr2021
2 18apr2021
2 20apr2021
2 25apr2021
3 29apr2021
3 03may2021
4 04may2021
;
run;
data want;
do until(last.cust);
set temp;
by cust;
if first.cust then first=dt;
end;
last=dt;
drop dt;
format first last date9.;
run;
One way:
proc summary data=temp nway; class cust; var dt; output out=want (drop=_: ) min=Fpd max=lpd; run;
Assuming the data has been sorted .
data temp;
input cust dt :date9.;
format dt date9.;
datalines;
1 01apr2021
1 02apr2021
1 10apr2021
2 15apr2021
2 18apr2021
2 20apr2021
2 25apr2021
3 29apr2021
3 03may2021
4 04may2021
;
run;
data want;
do until(last.cust);
set temp;
by cust;
if first.cust then first=dt;
end;
last=dt;
drop dt;
format first last date9.;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: