topic Re: Transforation of Data in SAS Programming

Transforation of Data

Aman4SAS — Mon, 26 Dec 2016 11:08:58 GMT

I have a data like

cust_id start_point count_1 count_2 count_3 count_4 count_5 count_6 count_7

a 3 . . 25 45 55 67 75

b 4 . . . 25 33 63 44

c 2 . 25 33 63 44 74 85

d 1 25 33 63 44 74 85 37

Need a data like

cust_id start_point M_0 M_1 M_2 M_3 M_4 M_5 M_6

a 3 25 45 55 67 75 . .

b 4 25 33 63 44 . . .

c 2 25 33 63 44 74 85 .

d 1 25 33 63 44 74 85 37

Please suggest

Re: Transforation of Data

Reeza — Mon, 26 Dec 2016 11:12:50 GMT

Why rename them?

Does position matter?

CALL SORT will SORT an array from smallest to largest and missing at the end.

Re: Transforation of Data

Aman4SAS — Mon, 26 Dec 2016 11:28:20 GMT

no its not about renaming and sorting,

suppose start point is 3rd month so month is third but for this custmer its first month so M_0 is 3rd month. like this

Re: Transforation of Data

Aman4SAS — Mon, 26 Dec 2016 11:34:23 GMT

i m trying like this but getting error please suggest

%macro loop();
Data want;
set have;
%do i = start_point %to 115;
m_%eval(&i.- start_point)=count_&i.;
%end;
run;
%mend;
%loop();

Re: Transforation of Data

Reeza — Mon, 26 Dec 2016 11:39:16 GMT

No macro.

Two arrays and a do loop. Here's untested sample to illustrate the idea.

Array orig(7) count1-count7;

array new(7) M0-M6;

K=1;

do i=1 to 7;

if orig( i ) Ne . Then do;

new(k)=orig(I);

k+1;

end;

Re: Transforation of Data

Aman4SAS — Mon, 26 Dec 2016 11:43:43 GMT

here is one problem , missing value can be there in between , in this case that will miss.

like count_3 count_4 count_5

45 . 65

ur output will be

M_0 M_1 M_2

45 65

but output should e

M_0 M_1 M_2

45 . 65

matter is only starting point. data need to left align only.

Re: Transforation of Data

Aman4SAS — Mon, 26 Dec 2016 12:09:40 GMT

can you please help t resolve error in this

%macro loop();
Data want;
set have;
%do i = start_point %to 115;
m_%eval(&i.- start_point)=count_&i.;
%end;
run;
%mend;
%loop();

variable description start_point is a variable when account open

count_i& is a count of txn

please suggest

Re: Transforation of Data

Kurt_Bremser — Mon, 26 Dec 2016 14:40:19 GMT

For the 75632nd time:

ONE CANNOT USE DATASTEP VARIABLES IN MACRO LOGIC!

Absolutely no macro needed, use an array and shift to the front, starting at start_point. Just omit the test for a missing value.

Re: Transforation of Data

Aman4SAS — Mon, 26 Dec 2016 14:57:59 GMT

start point is not fixed so i doubt array would b useful here.

Re: Transforation of Data

Kurt_Bremser — Mon, 26 Dec 2016 15:11:14 GMT

do i = start_point to dim(array);
  array{i-start_point+1} = array{i};
end;
drop i;

Re: Transforation of Data

Tom — Mon, 26 Dec 2016 15:17:08 GMT

There is no need to use macro logic for this problem.

data want ;
  set have ;
  array old count1-count7;
  array new m_0-m_6 ;
  do i=1 to dim(new) while (start_point+i-1 <= dim(old));
    new(i)=old(start_point +i-1);
  end;
run;

Re: Transforation of Data

Aman4SAS — Mon, 26 Dec 2016 16:01:09 GMT

My apology, Thanks