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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.