DATA Step, Macro, Functions and more

How to process the variables in dataset using macro

Reply
Occasional Contributor
Posts: 5

How to process the variables in dataset using macro

Hello,

I am tying to shift the data in dataset using the macro. I can process the shifting using arrays, but was wondering if this could be done using Macros.

What I have - Dataset with fields - variables1 to variables12 (not stored inan  Array. Each value is in separate field)

What I want to do - Create a new dataset using above one and call the macro inside the data step to process the shifting

My problem -

(a) I want the Macro to use the variables from SET DATASET, do the processing and store the values back. I have 140 fields in the dataset and I do not want to send those as arguments to the macro.

(b) If I use CALL EXECUTE with the string as argument, I am getting 'Out of Memory' error as the data is huge.

Is it possible to process the data dynamically using macros for such scenario?

%MACRO SHIFT;

       %LET MTHCNT = %SYSFUNC(INTCK(MONTH, Date1, Date2));                   /* Date1, Date2 and all other variables from Dataset1  */

       %LET MTHCNT1=%EVAL(&MTHCNT. + 1);

       %IF &MTHCNT. <= 12 %THEN %DO;

           %DO M=&MTHCNT1. %TO 12 %BY 1;

               %LET SUB=%EVAL(&M - &MTHCNT.);

               Var2_&M  = Var2_&SUB;

               Var3_&M  = Var3_&SUB;

               Var4_&M  = Var4_&SUB;

            %END;

          %DO N=1 %TO &MTHCNT. %BY 1;

              Var2_&N  = 0;

              Var3_&N  = ' ';

              Var4_&N  = ' ';

           %END;

       %END;

       %ELSE %DO;

           %DO N=1 %TO 12 %BY 1;

               Var2_&N  = 0;

               Var3_&N  = ' ';

              Var4_&N  = ' ';

          %END;

       %END;

%MEND SHIFT;

DATA

    dataset

    ;

SET

    dataset1

    ;

  BY

    Var1

    ;

    CALL EXECUTE('%SHIFT');

    IF LAST.Va1 THEN CALL EXECUTE('OUTPUT;');

    IF LAST.Var1 THEN CALL EXECUTE('RUN;');

RUN;

PROC Star
Posts: 7,487

How to process the variables in dataset using macro

Can you provide a very brief before/after example (i.e., two data steps) showing how the data looks and how you want it to look?

Occasional Contributor
Posts: 5

Re: How to process the variables in dataset using macro

Sure art297. Please see -

Let's assume Dataset1 contains 1 record with following data -

Var1 = 123456

Date1 = 1JAN2011 

Date2 = 1JUN2011

Var1_1 = 1, Var1_2 = 2, Var1_3 = 3, Var1_4 = 4,, Var1_5 = 5, Var1_6 = 6, Var1_7 = 7, Var1_8 = 8, Var1_9 = 9, Var1_10 = 10, Var1_11 = 11, Var1_12 = 12

Now based on the difference between Date1 and Date2 in Months, I want to shift the data in the record.

MthCnt = Diff in months (Date2 and Date1) = 6 - 1 = 5

Diff = 5, so calculate values 6 to 12 as -

(I just realized that my program should decrement the value in DO loop instead of incrementing. i.e   %DO M=12 %TO. &MTHCNT1. %BY -1Smiley Wink

Var1_12 = Var1_7    ( 12- diff = 7)

Var1_11 = Var1_6    ( 11- diff = 6)

Var1_10 = Var1_5    ( 10- diff = 5)

Var1_9   = Var1_4    ( 9- diff = 4)

Var1_8   = Var1_3    ( 8- diff =3)

Var1_7   = Var1_2    ( 7- diff = 2)

Var1_6   = Var1_1    ( 6- diff = 1)

And initialize 1 to 5 values = 0

So my final dataste should look like -

Var1 = 123456

Date1 = 1JAN2011 

Date2 = 1JUN2011

Var1_1 = 0, Var1_2 = 0, Var1_3 = 0, Var1_4 = 0,, Var1_5 = 0,

Var1_6 = 1, Var1_7 = 2, Var1_8 = 3, Var1_9 = 4, Var1_10 = 5, Var1_11 = 6, Var1_12 = 7

New Contributor
Posts: 3

Re: How to process the variables in dataset using macro

this is one way you can do it.

data test;

Var1 = 123456;

Date1 = '1JAN2011'd;

Date2 = '1JUN2011'd;

Var1_1 = 1;

Var1_2 = 2;

Var1_3 = 3;

Var1_4 = 4;

Var1_5 = 5;

Var1_6 = 6;

Var1_7 = 7;

Var1_8 = 8;

Var1_9 = 9;

Var1_10 = 10;

Var1_11 = 11;

Var1_12 = 12;

run;

options symbolgen mprint merror mlogic;

%macro shift;

%* Get the list of the variables to be shifted;

proc contents data=test out=name(keep=name where=(name not in ('Var1','Date1','Date2'))); run;

%* Store each variable into a macro variable;

data _null_;

  set name nobs=cnt;

  call symput(compress('name'||put(_n_,best.)),name);

  if _n_ =cnt then call symputx('cnt',_n_);

run;

%* Create a macro variable storing the date difference;

data _null_;

  set test nobs=dt_cnt;

  x=intck('month',date1,date2);

   call symput(compress('dt_dif'||put(_n_,best.)),x);

   if _n_ =dt_cnt then call symputx('dt_cnt',_n_);

run;

%* Create the new dataset with the shift;

data new;

  set test;

%do j=1 %to &dt_cnt;

   %do i=1 %to &&dt_dif&j;

     &&name&i = 0;

   %end;

   %do i=&cnt %to %eval(&&dt_dif&j+1) %by -1;

     &&name&i = &&name&i - &&&dt_dif&j;

   %end;

   output;

%end;

run;

    

%mend;

%shift

Occasional Contributor
Posts: 5

Re: How to process the variables in dataset using macro

vrmkool - Thank you for your response.

I can not use SYMPUT because date1 and date2 data varies for each record.

Super User
Super User
Posts: 7,074

Re: How to process the variables in dataset using macro

Does not look like a macro issue at all.  You are just moving data around in the same row of a dataset.

data want ;

   set have ;

    array v var1_1 - var1_12 ;

    diff =  intck('month',date1,date2);

    do i = 12 by -1 while (i-diff > 0);

      v(i) = v(i-diff) ;

    end;

    do i = 1 to diff  ;

      v(i) = 0;

    end;

run;

What is it that will vary in the macro call?  The names of the array variables?  The upper limit of 12?

Occasional Contributor
Posts: 5

Re: How to process the variables in dataset using macro

Tom - Thank you for your response.

I know this can be done storing data in an Array. I just wanted to know if this could be done without using Array. Thanks!

Super User
Super User
Posts: 7,074

Re: How to process the variables in dataset using macro

It is not "stored" in an array. Arrays in SAS are just a tool to simplify referencing variables.

You should use macro language constructs to build code that cannot be done with the normal language.

Occasional Contributor
Posts: 5

How to process the variables in dataset using macro

ok got it. Will give this a try. Thank you Smiley Happy

Valued Guide
Posts: 765

Re: How to process the variables in dataset using macro

Hi ... I agree on the "not a macro problem" ...

data have;

Var1 = 123456;

Date1 = '1JAN2011'd; Date2 = '1JUN2011'd;

Var1_1 = 1; Var1_2 = 2;   Var1_3 = 3;   Var1_4 = 4;

Var1_5 = 5; Var1_6 = 6;   Var1_7 = 7;   Var1_8 = 8;

Var1_9 = 9; Var1_10 = 10; Var1_11 = 11; Var1_12 = 12;

format date: date9.;

run;

data want;

set have;

array v(12) var1_: ;

diff = intck('month',date1,date2);

do i = 1 to 12;

   v(i) = (i gt diff) * (v(i) - diff);

end;

drop i diff;

run;

Ask a Question
Discussion stats
  • 9 replies
  • 273 views
  • 3 likes
  • 5 in conversation