BookmarkSubscribeRSS Feed
shweta
Calcite | Level 5

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;

9 REPLIES 9
art297
Opal | Level 21

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?

shweta
Calcite | Level 5

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 -1;)

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

vrmkool
Calcite | Level 5

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

shweta
Calcite | Level 5

vrmkool - Thank you for your response.

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

Tom
Super User Tom
Super User

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?

shweta
Calcite | Level 5

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!

Tom
Super User Tom
Super User

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.

shweta
Calcite | Level 5

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

MikeZdeb
Rhodochrosite | Level 12

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1293 views
  • 3 likes
  • 5 in conversation