topic why it doesn't work when i place one variable in the ID statement in proc transpose ? in SAS Procedures

why it doesn't work when i place one variable in the ID statement in proc transpose ?

tianerhu — Tue, 04 May 2021 20:39:11 GMT

proc transpose data = cert.trials out = transtrials1;
var cholesterol triglyc uric;
id name testdate;
run;
proc print data = transtrials1;
run;

the output like this:

proc transpose data = cert.trials out = transtrials1;
var cholesterol triglyc uric;
id name ;
run;
proc print data = transtrials1;
run;

please look at the red part , I place the variable name instead of the two variables 'name' and ' testdate' , and then it doesn't work , why ?

Re: why it doesn't work when i place one variable in the ID statement in proc transpose ?

Reeza — Tue, 04 May 2021 20:42:21 GMT

What is the error? How does it not work?

Re: why it doesn't work when i place one variable in the ID statement in proc transpose ?

Tom — Tue, 04 May 2021 20:48:38 GMT

You cannot have two variables for JOHNSON. One for the first DATE and other for the next DATE.

Perhaps you want to move one of those two variables to a BY statement instead?

Re: why it doesn't work when i place one variable in the ID statement in proc transpose ?

ballardw — Tue, 04 May 2021 21:49:13 GMT

The important thing to do is look at your LOG.

ID with duplicates for the BY group are problems.

Consider this small example:

data junk;
   input x y z;
datalines;
1 2 3
1 2 4
1 2 5
2 1 3
2 2 5
;

proc transpose data=junk out=example;
   by x;
   id y;
   var z;
run;

Which in the log for proc transpose shows:

743     id y;
744     var z;
745  run;

ERROR: The ID value "_2" occurs twice in the same BY group.
ERROR: The ID value "_2" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      x=1
WARNING: 1 BY groups omitted due to earlier errors.

Did you see a similar set of ERROR: messages for values of NAME in your second example in your log.

There is an option for the Proc Transpose statement, LET that allows multiple identical values for the ID variable(s).

However the result is seldom what you actually want.

747  proc transpose data=junk out=example
748     let ;
749     by x;
750     id y;
751     var z;
752  run;

WARNING: The ID value "_2" occurs twice in the same BY group.
WARNING: The ID value "_2" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      x=1
NOTE: There were 5 observations read from the data set WORK.JUNK.
NOTE: The data set WORK.EXAMPLE has 2 observations and 4 variables.

The duplicate values are now warnings but look at the output data set and see if it looks like what you might want.