topic Re: How to process the variables in dataset using macro in SAS Programming

How to process the variables in dataset using macro

shweta — Fri, 29 Jul 2011 02:01:58 GMT

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;

%ELSE %DO;

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

Var2_&N = 0;

Var3_&N = ' ';

Var4_&N = ' ';

%END;

%MEND SHIFT;

DATA

dataset

;

SET

dataset1

;

Var1

;

CALL EXECUTE('%SHIFT');

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

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

RUN;

How to process the variables in dataset using macro

art297 — Fri, 29 Jul 2011 02:43:51 GMT

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?

Re: How to process the variables in dataset using macro

shweta — Fri, 29 Jul 2011 03:27:59 GMT

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

Re: How to process the variables in dataset using macro

vrmkool — Fri, 29 Jul 2011 04:55:48 GMT

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

Re: How to process the variables in dataset using macro

Tom — Fri, 29 Jul 2011 14:06:17 GMT

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?

Re: How to process the variables in dataset using macro

MikeZdeb — Fri, 29 Jul 2011 15:28:17 GMT

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;

Re: How to process the variables in dataset using macro

shweta — Fri, 29 Jul 2011 15:44:18 GMT

vrmkool - Thank you for your response.

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

Re: How to process the variables in dataset using macro

shweta — Fri, 29 Jul 2011 15:46:16 GMT

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!

Re: How to process the variables in dataset using macro

Tom — Fri, 29 Jul 2011 15:49:52 GMT

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.

How to process the variables in dataset using macro

shweta — Fri, 29 Jul 2011 15:51:34 GMT

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