Hey there, spending way too much time trying to find a workaround to this issue but is time to ask for support. I created a macro that transposed data from a wide format (variables names in columns, values in rows) to a long one (variable name in one column, values as columns per year and sequence). It works very well when values are unique, but failed for some datasets as there are multiple observations for same ID and YEAR_SEQUENCE. It does not really failed but got unwanted results. This is due to the LET function in PROC TRANSPOSE that allows the macro to continue (with warnings) but the desired output is not what I need as only the last row per ID is outputted. The main goal is to flag when there was a change in data values between the sequence numbers (e.g. 1 if there was a change between sequence 1 and sequence 2, or sequence 2 and sequence 3). This is a dummy example: As you can see, for ID 2, Var_A has values STEP_A and STEP_B for same period (year and sequence), likewise VAR_B has two values for same period. Any help will be pretty much appreciated. data table;
input ID $ ACC_DATE SEQUENCE VAR_A $ VAR_B ;
datalines;
1 202201 1 STEP_B 350
1 202201 2 STEP_B 500
1 202201 3 STEP_B 700
2 202201 1 STEP_A 20
2 202201 1 STEP_B 5
2 202201 2 STEP_A 20
2 202201 2 STEP_B 5
2 202201 3 STEP_A 20
2 202201 3 STEP_B 5 ; run;
PROC SORT DATA= TABLE FORCE; BY ID ACC_DATE SEQUENCE ; RUN;
proc transpose data = work.Table out = work.Table_T1 name= VARIABLE PREFIX=_ DELIM=WD let ;
by ID; var var_a var_b;
id ACC_DATE SEQUENCE; run; want ID Variable _202201_1 _202201_2 _202201_3 1 VAR_A STEP_B STEP_B STEP_B 1 VAR_B 350 500 700 2 VAR_A STEP_A STEP_A STEP_A 2 VAR_A STEP_B STEP_B STEP_B 2 VAR_B 20 40 80 2 VAR_B 5 10 10
... View more
