proc transpose with duplicate ID values

Question

I need help with proc transpose procedure in SAS. My code initially was:

proc transpose data=temp out=temp1; 
by patid;
var text;
Id datanumber;
run;

This gave me error "The ID value " " occurs twice in the same BY group". I modified the code to this:

proc sort data = temp; 
by patid text datanumber; 
run;

data temp; 
set temp by patid text datanumber; 
if first.datanunmber then n = 0; 
n+1; 
run;

proc sort data = temp; 
by patid text datanumber n; 
run;

proc transpose out=temp1 (drop=n) let;
by patid;
var text;
id datanumber;
run;

This is giving me error: variable n is not recognized. Adding a let option is giving a lot of error "occurs twice in the same BY group". I want to keep all id values.

Please help me in this.

Data Example: Patid Text

Answer 1

When you get that error it is telling you that you have multiple data points for one or more variables that you are trying to create. SAS can force the transpose and delete the extra datapoints if you add "let" to the proc transpose line.

Answer 2

Your data is possibly not unique? I created a dataset (with unique values of patid and datanumber) and the transpose works:

data temp (drop=x y);
do x=1 to 4;
    PATID='PATID'||left(x);
    do y=1 to 3;
        DATANUMBER='DATA'||left(y);
        TEXT='TEXT'||left(x*y);
        output;
    end;
end;
proc sort; by _all_; 
proc transpose out=temp2 (drop=_name_);
     by patid;
     var text;
     id datanumber;
run;

my recommendation would be to forget the 'n' fix and focus on making the data unique for patid and datanumber, a dirty approach would be:

proc sort data = temp nodupkey; 
by patid datanumber; 
run;

at the start of your code..