topic populate a variable wiìth number if a condition is verified in SAS Programming

populate a variable wiìth number if a condition is verified

Tecla1 — Wed, 18 Dec 2024 12:21:51 GMT

Hi to all,

I need you another time...

In the case a variable is not populate i need to put a number that is the maxmum of the variable +1. I try with this:


PROC SORT DATA= &nome_tab6.&load_date_g._3;
BY  descending TBU_NUMREK ;
DATA &nome_tab6.&load_date_g._4;
SET &nome_tab6.&load_date_g._3;
format c bestd5.;
RETAIN C;
c=first.TBU_NUMREK;
IF NOT MISSING(TBU_NUMREK) THEN TBU_NUMREK=TBU_NUMREK;
ELSE TBU_NUMREK=C+1;

RUN;

TBU_5CODUF	TBU_NUMREK
00845	7681
06064	7682
06084	7683
00687	7684
08534	7685
08535	7686
08540	7687
08545	??????
08550
08555
08560
08565
08570
08575
08580
08585
08590

Re: populate a variable wiìth number if a condition is verified

quickbluefish — Wed, 18 Dec 2024 13:04:37 GMT

There are some things wrong with your code - for one thing, you need a BY statement in your data step if you're going to use things like first.<variable> or last.<variable> -- in this case:

BY descending NUMREK;

Secondly, the way you're defining C is just going to give you either a 1 or 0 (first.numrek is true or first.numrek is false).

I am not totally sure what you're trying to do, but here's how I would suggest you modify your DATA step (in this case, you don't actually need the BY statement):


data have;
set have;
retain C;
if C=. then C=NUMREK;  * populate C with first non-missing NUMREK (i.e., largest available), then not again ;
NUMREK=coalesce(NUMREK, C+1);
run;

That does what you *asked* for, but I suspect that's not really what you want. For one thing, by ordering as DESCENDING, the values of NUMREK are getting smaller and smaller as you go through the data, so I'm not really sure what you mean by "the maximum of the variable + 1". The maximum value of NUMREK is the first non-missing value in your sorted dataset.

I imagine what you actually want is something like this:

proc sort data=have; by numrek; run;

data have;
set have;
retain C;
if numrek > . then c=numrek;
else numrek=c+1;
run;

This populates a missing numrek with the largest value seen so far + 1.

Re: populate a variable wiìth number if a condition is verified

PaigeMiller — Wed, 18 Dec 2024 13:04:02 GMT

Please provide example data as working SAS data step code (examples and instructions) and not as screen captures, not as Excel files, not as any other file attachment, not as copy/paste from Excel.

Re: populate a variable wiìth number if a condition is verified

Tom — Wed, 18 Dec 2024 13:11:48 GMT

If you want the maximum then why are you replacing it with a value that can only be 1 or 2? (or in your case missing since you forgot a BY statement).

The FIRST. flags created when using a BY statement in a data step are boolean flags. They will either be TRUE (represented by value 1 in this case) or FALSE (represented by value of 0). You want to remember the actual value, not the true/false flag variable's value.

data &nome_tab6.&load_date_g._4;
  set &nome_tab6.&load_date_g._3;
  if _n_=1 then max_value=TBU_NUMREK;
  if missing(TBU_NUMREK) then do;
     max_value+1;
     TBU_NUMREK=max_value;
  end;
run;

Note the use of the SUM STATEMENT

max_value+1;

implicitly sets the variable being accumulated into, MAX_VALUE, as retained.

Note there was no reason to attach the BEST5. format to the variable C. Why force SAS to display the values with only 5 characters?

Re: populate a variable wiìth number if a condition is verified

Tecla1 — Wed, 18 Dec 2024 13:23:06 GMT

Hi bluefish, many tnks, you are rigth, I was trying more solution but I know what you have explained so well... Now i try your kindly solution ....

Re: populate a variable wiìth number if a condition is verified

Tecla1 — Wed, 18 Dec 2024 13:32:06 GMT

Hi Tom many tnks for your kindly help!!! after your replay everything seems so simple !!! I tnks all community for the help everityme it give.... have a nice new Year !!!

Re: populate a variable wiìth number if a condition is verified

Tecla1 — Wed, 18 Dec 2024 13:34:11 GMT

Hi Tom, i need the format but it's non important for the result !! I have a question: why "first." wasn't a good solution?
Tnks a lot !!

Re: populate a variable wiìth number if a condition is verified

Tecla1 — Wed, 18 Dec 2024 13:41:38 GMT

Hi Tom, I see well your first replay. Tnks a lot. Tecla

Re: populate a variable wiìth number if a condition is verified

Tom — Wed, 18 Dec 2024 13:50:42 GMT

For FIRST. to work (or even exist) you need a BY statement.

Using FIRST. would be useful if you had a grouping variable that you wanted to use to find different maximum values for each group.

Re: populate a variable wiìth number if a condition is verified

Tecla1 — Wed, 18 Dec 2024 15:14:46 GMT

Tnks