Hi All
I have a requirement to look at three numeric fields. if field one is not populated and field two and three are, then field two and three must be their values minus one. in summary, if the values on the left are not populated, the the values on the right must be reduced by either one or two.
Source Data
v1 | v2 | v3 |
1 | 2 | 3 |
2 | 3 | |
3 | ||
1 | 3 |
Expected Reslts
v1 | v2 | v3 |
1 | 2 | 3 |
1 | 2 | |
1 | ||
1 | 2 |
With other variables, I meant variables which are not the vX variables, ones which indicate which position the items present should be in, as if you sorted the test data you presented then row 4 could appear after row 1, there is no indication in the data that that row should appear in position 4. This is key as not be able to move data will limit your options in how to process it. I mean you could do:
data have;
infile datalines dsd;
input v1 v2 v3;
datalines;
1,2,3
,2,3
,,3
1,,3
;
run;
data want;
set have;
retain lstv2 lstv3;
if _n_=1 then do;
lstv2=v2;
lstv3=v3;
end;
else do;
if v1=. then do;
v2=lstv2-1;
v3=lstv3-1;
lstv2=v2;
lstv3=v3;
end;
else if v1 ne . then do;
lstv2=v2;
lstv3=v3;
end;
end;
run;
I would also guess this is a bigger issue in that you are getting this type of structure in the first place. Go back and look at previous steps and see if, by changing the output, you can simplify this whole matter earlier on in the process.
Please clarify your test data, provide full test data in the form of a datastep:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...
Its a bit of a guess at the moment, for instance, why are they in that order, are the other variables? Why is row 4 var3=2, it should be 3 following the logic?
With other variables, I meant variables which are not the vX variables, ones which indicate which position the items present should be in, as if you sorted the test data you presented then row 4 could appear after row 1, there is no indication in the data that that row should appear in position 4. This is key as not be able to move data will limit your options in how to process it. I mean you could do:
data have;
infile datalines dsd;
input v1 v2 v3;
datalines;
1,2,3
,2,3
,,3
1,,3
;
run;
data want;
set have;
retain lstv2 lstv3;
if _n_=1 then do;
lstv2=v2;
lstv3=v3;
end;
else do;
if v1=. then do;
v2=lstv2-1;
v3=lstv3-1;
lstv2=v2;
lstv3=v3;
end;
else if v1 ne . then do;
lstv2=v2;
lstv3=v3;
end;
end;
run;
I would also guess this is a bigger issue in that you are getting this type of structure in the first place. Go back and look at previous steps and see if, by changing the output, you can simplify this whole matter earlier on in the process.
data have;
infile datalines dsd;
input v1 v2 v3;
datalines;
1,2,3
,2,3
,,3
1,,3
;
run;
data want;
set have;
array x{*} v1-v3;
n=nmiss(of x{*});
if n ne 0 then do;
do k=1 to dim(x);
if missing(x{k}) then i=k;
end;
do j=i to dim(x);
x{j}=x{j}-n;
end;
end;
drop n i j k;
run;
proc print;run;
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.