topic Re: Variable increment based on Macro variable in SAS Programming

Variable increment based on Macro variable

Banana19 — Wed, 08 Feb 2023 00:24:58 GMT

Hi all, I'm trying to increment a variable based on a macro variable. I tried to use the below code,

PROC SORT DATA=WORK.tab1;
BY ISSUE;
RUN;

DATA tab2;
SET WORK.tab1;
BY ISSUE;
RETAIN PK &MAX_PK1.;
IF FIRST.ISSUE THEN PK = &MAX_PK1. + 1;
RUN;

but it generates same number across every row. Could you please let me know what am I missing?

Thanks,

Banana

Re: Variable increment based on Macro variable

Tom — Wed, 08 Feb 2023 01:15:56 GMT

In that code you are appear to be using the macro variable MAX_PK1 as if it contained the NAME of a variable.

For example if MAX_PK1 has the value VAR1, like this:

%let max_pk1=VAR1;

Then the data step your macro logic generates would be:

DATA tab2;
SET WORK.tab1;
BY ISSUE;
RETAIN PK VAR1;
IF FIRST.ISSUE THEN PK = VAR1 + 1;
RUN;

If instead you have set MAX_PK1 to an actual number, like 100,

%let max_pk1=100;

then you would not want to use it in the RETAIN statement so that you could generate code like this instead.

DATA tab2;
SET WORK.tab1;
BY ISSUE;
RETAIN PK ;
IF FIRST.ISSUE THEN PK = 100 + 1;
RUN;

Either way it is obvious why PK is set to a constant. It is only set to a value on the first observation for each ISSUE group. And the value it is set to is always the same.

Please show sample simple example input and expected output. You only need about two or three observations per ISSUE value for two or three different values of ISSUE.

Re: Variable increment based on Macro variable

Banana19 — Wed, 08 Feb 2023 01:31:59 GMT

Thanks for the reply Tom!

&max_pk1 has numeric data 15.

Expected output;

ISSUE	PK
ABC1	16
ABC2	17
ABC3	18
ABC4	19

So whenever VARIABLE 'ISSUE' IS UNIQUE, THE PK variable should increment by 1. I'm taking the inital value as 15 so it should be populated as 15,16,17,18,19,.....

Re: Variable increment based on Macro variable

Tom — Wed, 08 Feb 2023 03:54:05 GMT

@Banana19 wrote:

Thanks for the reply Tom!

&max_pk1 has numeric data 15.

Expected output;

ISSUE PK

ABC1 16

ABC2 17

ABC3 18

ABC4 19

So whenever VARIABLE 'ISSUE' IS UNIQUE, THE PK variable should increment by 1. I'm taking the inital value as 15 so it should be populated as 15,16,17,18,19,.....

So before you try to use macro logic to generate SAS code you need to know what SAS code you need to generate. To do what you ask you just need code like this:

data want;
  set have;
  by issue;
  retain pk 15 ;
  if first.issue and _n_>1 then pk+1;
run;

To make it dynamic just replace the 15 with the macro variable reference.

Re: Variable increment based on Macro variable

Banana19 — Wed, 08 Feb 2023 13:37:19 GMT

Thank you,Tom! It worked!!