DATA Step, Macro, Functions and more

Transforation of Data

Accepted Solution Solved
Reply
Super Contributor
Posts: 266
Accepted Solution

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 

 

Please suggest

 

 


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

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;

View solution in original post


All Replies
Super User
Posts: 19,860

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: 266

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: 266

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: 19,860

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: 266

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: 266

Re: Transforation of Data

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

Super User
Posts: 7,854

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
Super Contributor
Posts: 266

Re: Transforation of Data

Posted in reply to KurtBremser

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

Super User
Posts: 7,854

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
Super Contributor
Posts: 266

Re: Transforation of Data

Posted in reply to KurtBremser

My apology, Thanks

Solution
‎12-26-2016 11:02 AM
Super User
Super User
Posts: 7,074

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 314 views
  • 0 likes
  • 4 in conversation