Hello !
I've got a little question about a problem met in my work today. I've got a SAS table which looks like the following one :
Nosinid | ndep | dep |
---|---|---|
1 | 1 | 100 |
1 | 2 | -100 |
1 | 3 | 100 |
2 | 1 | 500 |
2 | 2 | -500 |
2 | 3 | 500 |
2 | 4 | -500 |
2 | 5 | 500 |
2 | 6 | 500 |
3 | 1 | 1000 |
3 | 2 | -500 |
3 | 3 | 500 |
I need, for each value of the variable Nosinid, to read the values of dep in the order of ndep, and to delete the pairs (a,-a) with a>0 and the two elements a and -a are consecutive. So the table should become the following one :
Nosinid | ndep | dep |
---|---|---|
1 | 3 | 100 |
2 | 5 | 500 |
2 | 6 | 500 |
3 | 1 | 1000 |
3 | 2 | -500 |
3 | 3 | 500 |
Could you explain me how I could perform this operation ? Thanks in advance.
Ludovic
There are a number of ways to do what you ask. Here is one method:
data have;
input Nosinid ndep dep;
cards;
1 1 100
1 2 -100
1 3 100
2 1 500
2 2 -500
2 3 500
2 4 -500
2 5 500
2 6 500
3 1 1000
3 2 -500
3 3 500
;
data want (drop=skip next:);
set have;
by Nosinid;
retain skip;
set have ( firstobs = 2 keep = ndep dep
rename = (ndep = next_ndep
dep = next_dep) )
have ( obs = 1 drop = _all_ );
if first.Nosinid then skip=0;
next_ndep = ifn( last.Nosinid, (.), next_ndep );
next_dep = ifn( last.Nosinid, (.), next_dep );
if skip then skip=0;
else do;
if dep gt 0 and sum(dep,next_dep) eq 0 then skip=1;
if not skip then output want;
end;
run;
hi ... this gives the ouput you specified
I added an extra set of observations where the (a, -a) pair crosses values of NOSINID ...
data have;
input Nosinid ndep dep;
cards;
1 1 100
1 2 -100
1 3 100
2 1 500
2 2 -500
2 3 500
2 4 -500
2 5 500
2 6 500
3 1 1000
3 2 -500
3 3 500
4 1 -500
4 2 100
4 3 100
;
run;
data want (drop=n d);
do j=1 to obs;
set have nobs=obs point=j;
k = j + 1;
set have (keep=nosinid dep rename=(nosinid=n dep=d)) point = k;
if (nosinid eq n) and (dep gt 0) and (dep+d eq 0) then j + 1;
else output;
end;
stop;
run;
Nosinid ndep dep
1 3 100
2 5 500
2 6 500
3 1 1000
3 2 -500
3 3 500
4 1 -500
4 2 100
4 3 100
data have; input Nosinid ndep dep; cards; 1 1 100 1 2 -100 1 3 100 2 1 500 2 2 -500 2 3 500 2 4 -500 2 5 500 2 6 500 3 1 1000 3 2 -500 3 3 500 ; run; data want(drop=_: flag); merge have have(firstobs=2 keep=dep Nosinid rename=(dep=_dep Nosinid=_Nosinid)); retain flag 0; if dep gt 0 and dep+_dep=0 and Nosinid=_Nosinid then do; flag=1;delete; end; if flag then do;flag=0;delete; end; run;
Ksharp
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.