## Transforation of Data

Solved
Super Contributor
Posts: 271

# Transforation of Data

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

Accepted Solutions
Solution
‎12-26-2016 11:02 AM
Super User
Posts: 8,117

## Re: Transforation of Data

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;``````

All Replies
Super User
Posts: 23,771

## Re: Transforation of Data

Why rename them?

Does position matter?

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

Super Contributor
Posts: 271

## Re: Transforation of Data

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

Super Contributor
Posts: 271

## Re: Transforation of Data

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

Super User
Posts: 23,771

## Re: Transforation of Data

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;

end;

Super Contributor
Posts: 271

## Re: Transforation of Data

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.

Super Contributor
Posts: 271

## Re: Transforation of Data

%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

Super User
Posts: 10,278

## Re: Transforation of Data

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 271

## Re: Transforation of Data

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

Super User
Posts: 10,278

## Re: Transforation of Data

```do i = start_point to dim(array);
array{i-start_point+1} = array{i};
end;
drop i;```
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 271

## Re: Transforation of Data

My apology, Thanks

Solution
‎12-26-2016 11:02 AM
Super User
Posts: 8,117

## Re: Transforation of Data

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;``````
☑ This topic is solved.