BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mick_bill
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

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;

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Mick_bill
Fluorite | Level 6
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
Diamond | Level 26

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

--
Paige Miller
Tom
Super User Tom
Super User

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
Meteorite | Level 14

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 599 views
  • 0 likes
  • 4 in conversation