Going backwards select the first non negative number SAS

Question

I have a dataset that looks similar to the below:

ID  COL70  COL71 COL72 COL73 COL74
1     4      3     2    -998   .
2     2      0     2      1   -998
3     1     -998  -998    .    .
4     3      4    -998  -998  -998

What I want to do is have a new columns lets call it NEWCOL that has the first non negative value looking backwards from COL74, so it would look as follows:

ID  COL70  COL71 COL72 COL73 COL74  NEWCOL
1     4      3     2    -998   .      2 
2     2      0     2      1   -998    1 
3     1     -998  -998    .    .      1
4     3      4    -998  -998  -998    4

I'm working from WPS so this would need to be in SAS or PROC SQL please.

@Reeza I didn't know where to start so I hadn't tried anything before hand. That's why I posted the question. I didn't realise I had to have tried something to be able to post a question? — MLPNPC, Jan 14 '19 at 11:43
Does anyone know why the question's been down voted? I'm looking to improve the way I ask questions if I've done anything wrong? Thanks — MLPNPC, Jan 14 '19 at 11:50
Most likely because you haven't shown any attempt. Yes, you're supposed to have attempted to solve the problem first yourself in some manner. Please see the guidelines on how to ask a question [ask]. — Reeza, Jan 14 '19 at 16:32
Thanks for the response @Reeza , I'll try and post links to questions that haven't solved the problem in the future as sometimes I have no idea where to start and that's why I post the question. But I'll try and include questions that don't quite fit what I need. Thanks again. — MLPNPC, Jan 15 '19 at 11:55

score 5 · Accepted Answer · answered Jan 09 '19 at 14:50

You can iterate over an array like this.

data have;
   array c[*] col70-col74;
   input id c[*];
   do i = dim(c) to 1 by -1 until(sign(c[i]) eq 1); 
      newcol=c[i]; 
      end;
   if i eq 0 then newcol=.;
   cards;
1     4      3     2    -998   .
2     2      0     2      1   -998
3     1     -998  -998    .    .
4     3      4    -998  -998  -998
5     -3     -4    -998  -998  -998
;;;;
   run;
proc print;
   run;

score 0 · Answer 2 · answered Jan 09 '19 at 15:07

0

I figured it out for anyone wanting the same:

data want;
set have;
temp = 0;
num = 74;
array col{70:74} col70-col74;
do while (temp = 0);
if col{num} >= 0 then do;
newvar = col{num};
temp = 1;
end;
else num = num - 1;
end;
run;

answered Jan 09 '19 at 15:07

MLPNPC

454
5
18

score 0 · Answer 3 · answered Jan 09 '19 at 20:45

0

In SQL a simple case expression can 'backup' to the desired result

proc sql;
  create table want as
  select have.*
  ,
  case
    when col74 > 0 then col74
    when col73 > 0 then col73
    when col72 > 0 then col72
    when col71 > 0 then col71
    when col70 > 0 then col70
    else .
  end as last_positive
  from have
  ;
quit;

answered Jan 09 '19 at 20:45

Richard

25,390
3
25
38

The question holds only a subset of the actual dataset, I have over 104 columns in total so doing a case when for 104 columns wouldn't work. – MLPNPC Jan 14 '19 at 11:44
1

Why wouldn't it work ? The SQL case statement has no limitation. If need be you can write a macro to generate the necessary `when` clauses. An actual problem having > 104 similar columns sounds like your data model has 2 years of weekly data as columns and that might be hindering coding up cleaner time-dimensional analysis or updating the table(s) with next weeks data. SQL does not have arrays so SQL select is coded with the wallpaper `case`. – Richard Jan 14 '19 at 13:55
I need to keep all columns for business reasons. It wouldn't work for me as I don't think it's best practice to code up case when over a 100 times? – MLPNPC Jan 14 '19 at 15:45

Going backwards select the first non negative number SAS

3 Answers3