topic Re: How to subract the last date from the first date for each row in SAS Programming

How to subract the last date from the first date for each row

Melika2 — Tue, 23 Feb 2021 15:36:17 GMT

I have a dataset similar to the one shown below, where I have transposed the data in chrongical order based on date. My dates will be different every time though.. since I will be running a query everyday so the dates will update. For each ID, I need to subtract the latest date( amount)- First date (amount)/ First date to find the percentage of change over that time period. SO for ID1 ( 138-0)/0 but I will need to set this to 100% for ID2 (149-145)/145.

data test;

input ID sasdate date9. amount;

format sasdate date9.;

datalines;

1 01FEB2014 123

1 01MAR2014 138

2 01JAN2014 145

2 01FEB2014 155

2 01MAR2014 149

3 01JAN2014 133

3 01FEB2014 132

3 01MAR2014 129

;

run;

proc sort data=test;

by id sasdate;

run;

proc sql;

select distinct cats('_',put(sasdate,date9.))

into :alldates separated by ' '

from test

order by sasdate;

quit;

%put &alldates;

proc transpose data=test out=outx1 (drop=_name_);

by id;

var amount;

id sasdate;

idlabel sasdate;

run;

data outx1;

retain id &alldates;

set outx1;

run;

proc print data=outx1 label noobs ;

title 'Transposed dates in chronological order';

run;

Re: How to subract the last date from the first date for each row

PaigeMiller — Tue, 23 Feb 2021 16:05:33 GMT

... SO for ID1 ( 138-0)/0 ...

I don't understand this part. Shouldn't it be (138-123)/123 for ID1?

Re: How to subract the last date from the first date for each row

Melika2 — Tue, 23 Feb 2021 16:23:11 GMT

it should be the earliest date which is 01JAN2014 and since there was no amount for that.. it should be set to 0. The earliest date associated for the whole dataset not the particular group.

Re: How to subract the last date from the first date for each row

PaigeMiller — Tue, 23 Feb 2021 16:33:02 GMT

proc summary data=test;
    class id;
    var sasdate amount;
    output out=summary maxid(sasdate(amount))=max_date_amount minid(sasdate(amount))=min_date_amount 
        max(sasdate)=max_date min(sasdate)=min_date;
run;
data want;
    if _n_=1 then set summary(where=(_type_=0) rename=(min_date=global_min_date) keep=min_date _type_);
    set summary(where=(_type_=1));
    if min_date=global_min_date then percent=(max_date_amount-min_date_amount)/min_date_amount;
    else percent=1;
    format percent percentn8.2;
    drop _type_;
run;

Re: How to subract the last date from the first date for each row

mkeintz — Tue, 23 Feb 2021 17:07:54 GMT

Very nice. No pre-sorting required, and getting the global min date is integrated in use of the proc summary.

Re: How to subract the last date from the first date for each row

PaigeMiller — Tue, 23 Feb 2021 17:29:15 GMT

Thanks!

More people should grasp the power of PROC SUMMARY. Hardly a day goes by when I don't use it (except weekends and holidays)

Re: How to subract the last date from the first date for each row

Melika2 — Tue, 23 Feb 2021 19:09:50 GMT

This works! Thank you so much... what if I want to group by two class variables?

Re: How to subract the last date from the first date for each row

mkeintz — Tue, 23 Feb 2021 19:45:02 GMT

@Melika2 wrote:
This works! Thank you so much... what if I want to group by two class variables?

Say you use CLASS A B: . Then proc summary would generate 4 types of observations (indicated by the _TYPE_ variable):

Each non-empty A*B cell. _TYPE_=3
Each Class A value (summed over all B values) - think row marginals - there will be N(a) of these, with _TYPE=1 or 2, I forget which.
Each Class B value (summed over all A values) - think column marginals - there will be N(b) of these with _TYPE_=2 or 1
Global (one cell) _TYPE_=0

The specific PROC SUMMARY feature used for this problem by @PaigeMiller was the MINID option, as in

minid(sasdate(amount))=min_date_amount

which records the amount corresponding to the minimum sasdate (within each CLASSification). I had been unaware of MINID (or maybe I forgot - I can't remember) .

So not only did proc summary provide the minimum date via

min(sasdate)=min_date

allowing you to trivially determine whether a given ID has a starting date equivalent to the overall starting date, but you have the corresponding amount available when needed.

Re: How to subract the last date from the first date for each row

PaigeMiller — Tue, 23 Feb 2021 20:40:03 GMT

To do this for 2 ID variables, I think only two changes are needed.

proc summary data=test;
    class id1 id2;
    var sasdate amount;
    output out=summary maxid(sasdate(amount))=max_date_amount minid(sasdate(amount))=min_date_amount 
        max(sasdate)=max_date min(sasdate)=min_date;
run;
data want;
    if _n_=1 then set summary(where=(_type_=0) rename=(min_date=global_min_date) keep=min_date _type_);
    set summary(where=(_type_=3));
    if min_date=global_min_date then percent=(max_date_amount-min_date_amount)/min_date_amount;
    else percent=1;
    format percent percentn8.2;
    drop _type_;
run;

I think if you have a large data set, you can make PROC SUMMARY more efficient by adding in the WAYS statement.

ways 0 2;

Re: How to subract the last date from the first date for each row

ChrisHemedinger — Tue, 23 Feb 2021 20:44:58 GMT

I think that @PaigeMiller and @mkeintz have forgotten more about PROC SUMMARY than I ever knew (or probably ever will know). Talk about achieving the goal with the fewest PROCs / passes through the data!

Re: How to subract the last date from the first date for each row

mkeintz — Wed, 24 Feb 2021 04:38:21 GMT

This is an example of why I try to convince programming colleagues to think of SAS as a tool first, a programming language second (and really it's the DATA step that I find is most like a language).