Hi I am trying to find out difference between dates as per Name.
data test;
INPUT NAME $ DOB;
INFORMAT DOB DATE9.;
FORMAT DOB DATE9.;
DATALINES;
NITISH 10MAR2016
PRIYA 10DEC2016
PRIYA 01JAN2016
SAKSHI 20DEC2016
SAKSHI 20APR2016
SAKSHI 02JAN2016
;
RUN;
So output will be like
NAME DIFF
NITISH (10MAR2016-10MAR2016) i.e. 0 days
PRIYA (10DEC2016-10DEC2016)
SAKSHI (20DEC2016-20APR2016)
SAKSHI (20DEC2016-02JAN2016)
SAKSHI (20APR2016-02JAN2016)
Please help me to reach to solution. Itried using proc sort and appling lag in my dataset but was not able to achieve the results
I think you're looking for something like:
proc sql; create table want as select a.*, count(a.name) as count, b.dob as dob2, abs(dob2-a.dob) as diff from test a join test b on a.name eq b.name group by a.name order by a.id ; quit;
Art, CEO, AnalystFinder.com
I don't understand your desired results. Priya, for example, has "PRIYA (10DEC2016-10DEC2016)" but I don't see any result for her second row of data with 01JAN2016.
Shouldn't there be a result for PRIYA 10DEC2016 and 01JAN2016 with a difference of 344 days?
If you can tighten up what you expect to see, it will be easier to come up with a solution.
My apologies if I simply don't understand.
Thanks,
Collin
Sorry for the typo the output will be like
NAME DIFF
NITISH (10MAR2016-10MAR2016) i.e. 0 days
PRIYA (10DEC2016-01JAN016)
SAKSHI (20DEC2016-20APR2016)
SAKSHI (20DEC2016-02JAN2016)
SAKSHI (20APR2016-02JAN2016)
This would ne my deisre reult. Please Collin if you check it and let me know how to solve this problem.
Thanks
Nitish
You want to calculate the maximum date range of DOB for each name, and then assign that diff to every record in the data set. If your data test is grouped (but not neccessarily in ascending or descending order) by name, then:
data want (drop=mindob maxdob);
do until (last.name);
set test;
by name notsorted;
mindob=min(mindob,dob);
maxdob=max(maxdob,dob);
end;
diff=maxdob-mindob;
do until (last.name);
set test;
by name notsorted;
output;
end;
run;
This program;
I think you're looking for something like:
proc sql; create table want as select a.*, count(a.name) as count, b.dob as dob2, abs(dob2-a.dob) as diff from test a join test b on a.name eq b.name group by a.name order by a.id ; quit;
Art, CEO, AnalystFinder.com
Hi Sir
Your solution allmost worked for me but facing one issue.I am getting duplicate data i.e.
Example for Priya
Name DOB count DOB2 diff
Priya 01JAN2016 4 10DEC2016 344
Priya 10DEC2016 4 01JAN2016 344
Is there way to Over come this because i want only one row.
Is the following what you want?:
data test; INPUT NAME $ DOB; INFORMAT DOB DATE9.; FORMAT DOB DATE9.; id=_n_; DATALINES; NITISH 10MAR2016 PRIYA 10DEC2016 PRIYA 01JAN2016 SAKSHI 20DEC2016 SAKSHI 20APR2016 SAKSHI 02JAN2016 ; proc sql; create table want as select a.*, count(a.name) as count, b.dob as dob2, abs(dob2-a.dob) as diff from test a join test b on a.name eq b.name group by a.name having a.id le b.id order by a.id ; quit;
Art, CEO, AnalystFinder.com
The reason you saw duplicates in the output of @art297's first response is because that is how you displayed the desired output in your initial post.
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.