proc transpose with duplicate ID values

0
votes

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

4
sastransposeproc-sql
Nupur, can you give an example of your data? I was unable to replicate your error. – RWill
Sure. I am trying to make a small table here to show you how my data looks like but am not able to do that. can you tell me how can i do this? – Nupur
Just something simple, like: options obs=10; proc print data=temp; var patid text datanumber; – RWill
Nupur, I still haven't been able to make a dataset to replicate your error, but here are a few suggestions. 1) In the second code box you need to add a semicolon between the TEMP dataset name and the BY statement. 2) Correct the spelling of FIRST.DATANUMBER in the IF statement. 3) You probably need to use a RETAIN N statement for what you are attempting. If you can provide an example of your actual data, then we can diagnose the errors better. – RWill
Hi RWill Thanks so much for your reply. Your suggestions worked :)... I wanted to put everything in one row for each observation so that the each patid will have one row with associated datanumbers. The code has transposed my data with datanumbers being as variables now but I still have same number of rows as I had before for patid. The values for datanumbers (as variables) are in different lines for each patid. Is there a way to fix that? Please let me know if I didnt explain it in an understandable manner. Thanks again for all your help – Nupur

4 Answers

2
votes

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.

0
votes

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..

0
votes

Try to sort your dataset by patid text n datanumber, (n before datanumber).

0
votes

Try to sort your dataset by patid n datanumber, (n before datanumber). and proc transpose "by patib n ";