%macro naming variables within a do loop

[Mick_bill]

Hello,

I have Ids which go across time and i want to get them on to one line, but first i need to rename the data. This is where the issues is. 

I have an Id which appears multple times, and has over lapping data.

E.g. ID 1 but period 1 and 2. Both come with data for Z1 - Z13
however Z2-13 for period 1 and Z1-12 period 2 are the same. I have split the data up into different time frames, so unquie IDS and all i want to do is rename the Z variables to reflect the same time periods. Period 2 would now be names Z2-14 rather than 1-13. 

I have put a quick example of what I am after below. Can someone tell me why the macro is not working for the rename.

The issues is the Do loop continues to go round when the criteria doesnt match this and i do not know why.

data original_ALL;

input ID Year_period $ Period Year Z1 Z2 Z3 ;

datalines;

1 2019_01 1 2019 100 101 102

1 2019_02 2 2019 200 201 202

3 2019_01 1 2019 1 2 3

;

run;

data original1;

input ID Year_period $ Period Year Z1 Z2 Z3 ;

datalines;

1 2019_01 1 2019 100 101 102

3 2019_01 1 2019 1 2 3

;

run;

data WANT1;

input ID Year_period $ Start_period Year Period1 Period2 Period3 ;

datalines;

1 2019_01 1 2019 100 101 102

3 2019_01 1 2019 1 2 3

;

run;

data original2;

input ID Year_period $ Period Year Z1 Z2 Z3 ;

datalines;

1 2019_02 2 2019 101 102 103

;

run;

data WANT2;

input ID Year_period $ start_period year Period2 Period3 Period4 ;

datalines;

1 2019_02 2 2019 101 102 103

;

run;

data all_group (keep = id Period1              Period2 Period3 Period4) a_only b_only; merge want1 (in=a) want2 (in=b);

by id;

if a and b then output all_group;

if a and not b then output a_only;

if b and not a then output b_only;

run;

%macro rename;

Z1 = Period%eval(&j.)

Z2 = Period%eval(&j.+1)

Z3 = Period%eval(&j.+2)

%mend;

%MACRO RENAME_DS;

                DATA WORK.test; SET WORK.original1;

%let PERIOD = PERIOD;

%do year = 2019 %to 2020;         

                                %IF &YEAR = 2019 %THEN %DO;

                                                %DO j = 1 %TO 13;

                                                                IF &PERIOD. = &j. THEN DO;

                                                                                RENAME

                                                                                %RENAME;

                                                                END;

                                                %END;

                                %END; 

%end;                  

                                ELSE IF YEAR = 2020 THEN DO;

                                                %DO j = 14 %TO 26;

                                                                RENAME

                                                                %RENAME;

                                                %END;                 

                                END;

RUN;

%MEND RENAME_DS;

%RENAME_DS;

[PaigeMiller]

Just a suggestion ... these types of issues are usually much more easily handled with a long data set instead of wide data set where you have to write complicated macros to handle the naming.

If your macro is successful at renaming, then what? What is the next step of data analysis or reporting? Usually the next step is much easier to code with a long data set than a wide data set.

[Mick_bill]

I have roughly 26 periods which have been separated and will have to be renamed. Otherwise this will require 26 different renaming codings. There are also much more than the 1 variable listed in the example.

So I am trying to speed it up. I will then bring the data together.

The issue is that the ids aren't consistent and always available for the each month. Or I would just be able to take the final month. So I need to rename all and take the data where it js available.

If this is not the best way. I will have to do manually.

The issue is around the criteria not stopping the macro.

[PaigeMiller]

Long, not wide. Most of these problems go away.

[Tom]

I don't get what you are trying to do from your example datasets.

For example the transformation from ORIGINAL1 -> WANT1 seems to be just renaming some variables.

data want1;
  set original1;
  rename period=start_period z1-z3=period1-period3 ;
run;

What is the actual input?  Is it ORIGINAL_ALL?  Or ORIGiNAL1 and ORIGINAL2?  Or all three?

What is the actual output you would want from this data?

[s_lassen · Accepted solution]

Instead of splitting your data and renaming the variables, why don't you use arrays to get the result you want?

data want;                             
  set original_All;                    
  array periods(*) 8  period1-period4; 
  array invars(*) z1-z3;                  
  do _N_=1 to dim(invars);             
    periods(_N_+period-1)=invars(_N_); 
    end;                               
  rename period=start_period;          
  drop z1-z3;                          
run;