Hello, SAS experts.
I have following data.
date | group | value |
19910731 | 1 | 23 |
19920731 | 1 | 43 |
19930731 | 1 | 54 |
19910731 | 2 | 34 |
19920731 | 2 | 23 |
19930731 | 2 | 43 |
19910731 | 3 | 23 |
19920731 | 3 | 34 |
19930731 | 3 | 23 |
I would like to add to this dataset group 4 with the values equals to the difference of values of group=1 and group=2 at the same date.
date | group | value |
19910731 | 1 | 23 |
19920731 | 1 | 43 |
19930731 | 1 | 54 |
19910731 | 2 | 34 |
19920731 | 2 | 23 |
19930731 | 2 | 43 |
19910731 | 3 | 23 |
19920731 | 3 | 34 |
19930731 | 3 | 23 |
19910731 | 4 | 11 |
19920731 | 4 | -23 |
19930731 | 4 | -11 |
As the final output I would like to have a table with dates as rows and groups as columns. But the group 4 I would like to rename as "difference":
Group 1 | Group 2 | Group 3 | Difference | |
19910731 | 23 | 34 | 23 | 11 |
19920731 | 43 | 23 | 34 | -23 |
19930731 | 54 | 43 | 23 | -11 |
Could somebody help me with code.
If this is what you want, then try:
data have ;
input date group value ;
cards ;
19910731 1 23
19920731 1 43
19930731 1 54
19910731 2 34
19920731 2 23
19930731 2 43
19910731 3 23
19920731 3 34
19930731 3 23
run ;
data step1 ;
do _n_ = 1 by 1 until (last.group) ;
set have ;
by group ;
output ;
array v [3,3] _temporary_ ;
v[group,_n_] = value ;
end ;
if group = 3 then do _n_ = 1 to _n_ ;
set have (keep = date) ;
group = 4 ;
value = v[2,_n_] - v[1,_n_] ;
output ;
end ;
run ;
Kind regards
Paul D.
Not sure what the value -23 for the difference is doing in your suggested output, as 23 - 43 = -20.
At any rate, assuming this is a typo, methinks the following should do it in one fell swoop and in the requisite output file format:
data have ;
input date group value ;
cards ;
19910731 1 23
19920731 1 43
19930731 1 54
19910731 2 34
19920731 2 23
19930731 2 43
19910731 3 23
19920731 3 34
19930731 3 23
run ;
data want (drop=group) ;
merge have (where=(group=1) rename=value=group1)
have (where=(group=2) rename=value=group2)
have (where=(group=3) rename=value=group3)
;
by date ;
difference = group2 - group1 ;
run ;
Note: BY DATE is included just in case you may have other (identically structured) records with different date values.
Kind regards
Paul D.
Thank you for you answer.
But this advice misses one step. First I want to get the same data, just with one more group type added (group 4).
date | group | value |
19910731 | 1 | 23 |
19920731 | 1 | 43 |
19930731 | 1 | 54 |
19910731 | 2 | 34 |
19920731 | 2 | 23 |
19930731 | 2 | 43 |
19910731 | 3 | 23 |
19920731 | 3 | 34 |
19930731 | 3 | 23 |
19910731 | 4 | 11 |
19920731 | 4 | -20 |
19930731 | 4 | -11 |
As Reeza said.
data have ;
input date group value ;
cards ;
19910731 1 23
19920731 1 43
19930731 1 54
19910731 2 34
19920731 2 23
19930731 2 43
19910731 3 23
19920731 3 34
19930731 3 23
run ;
proc sort data=have out=temp;
by date;
run;
proc transpose data=temp out=want prefix=group;
by date;
id group;
var value;
run;
Nice, as it internally automates the creation of group1, group2, etc.
But you need another step to compute group2-group1=difference.
Kind regards
Paul D.
Thank you for you answer.
But this advice misses one step. First I want to get the same data, just with one more group type added (group 4).
date | group | value |
19910731 | 1 | 23 |
19920731 | 1 | 43 |
19930731 | 1 | 54 |
19910731 | 2 | 34 |
19920731 | 2 | 23 |
19930731 | 2 | 43 |
19910731 | 3 | 23 |
19920731 | 3 | 34 |
19930731 | 3 | 23 |
19910731 | 4 | 11 |
19920731 | 4 | -20 |
19930731 | 4 | -11 |
If this is what you want, then try:
data have ;
input date group value ;
cards ;
19910731 1 23
19920731 1 43
19930731 1 54
19910731 2 34
19920731 2 23
19930731 2 43
19910731 3 23
19920731 3 34
19930731 3 23
run ;
data step1 ;
do _n_ = 1 by 1 until (last.group) ;
set have ;
by group ;
output ;
array v [3,3] _temporary_ ;
v[group,_n_] = value ;
end ;
if group = 3 then do _n_ = 1 to _n_ ;
set have (keep = date) ;
group = 4 ;
value = v[2,_n_] - v[1,_n_] ;
output ;
end ;
run ;
Kind regards
Paul D.
Thank you! That is what I needed.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.