topic Re: Duplicate errors with Proc Transpose in SAS Programming

Duplicate errors with Proc Transpose

mreynaud — Tue, 01 Jun 2021 22:34:23 GMT

Hello,

I've been exploring proc transpose and want a narrow data set to become wide. I've been working with a data set and running into an error. For the sake of this example I will use data from sashelp.cars

I would like to create columns with the name of each country from origin and then the make of the cars as the observations under each country.

When I run this code:

proc sql;
create table test as
select distinct 
	Origin,
	Make
from sashelp.cars;
quit;

proc sort data=test out=test_sort;
by origin;
run;

proc transpose data=test_sort
			   out=test_transp
			   name=column_that_was_transposed;
id origin;
run;

I receive this error: ERROR: The ID value "Asia" occurs twice in the input data set.

To combat it I added a nodupkey option in the proc sort step:

proc sql;
create table test as
select distinct 
	Origin,
	Make
from sashelp.cars;
quit;

proc sort data=test out=test_sort nodupkey;
by origin;
run;

proc transpose data=test_sort
			   out=test_transp
			   name=column_that_was_transposed;
id origin;
run;

It runs but the table has no observations. Any help would be much appreciated.

Re: Duplicate errors with Proc Transpose

Reeza — Tue, 01 Jun 2021 22:45:33 GMT

What are you expecting?
Do you want each variable to be the origin and then a list of models underneath like:

Asia    |Europe       |USA
---------------------------------
Acura  |Audi           |Buick
Honda  |BMW            |Cadillac

Re: Duplicate errors with Proc Transpose

ballardw — Tue, 01 Jun 2021 22:49:01 GMT

You might show what you expect or want the output to look like.

When I run your code this the LOG from the Transpose step:

320  proc transpose data=test_sort
321              out=test_transp
322              name=column_that_was_transposed
323
324  ;
325  id origin;
326  run;

NOTE: No variables to transpose.
NOTE: There were 3 observations read from the data set WORK.TEST_SORT.
NOTE: The data set WORK.TEST_TRANSP has 0 observations and 4 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

If you do not supply one or more variables on a VAR statement in Proc Transpose then the procedure assumes you want to transpose all of the numeric variables. Since your data set has no numeric variables there is nothing to transpose.

If you run

proc transpose data=test_sort
			   out=test_transp
			   name=column_that_was_transposed 
  
;
var make;
id origin;
run;

then you will get one "make" value for each ID variable value in the data.

Do you actually need a data set? You might try adding a dummy variable to use as a "by" variable to get around the multiple ID values.

proc sql;
create table test as
select distinct 
	Origin,
	Make

from sashelp.cars;
quit;

data test_dummy;
  set test;
  by origin;
  if first.origin then dummy=1;
  else dummy+1;
run;

proc sort data=test_dummy;
   by dummy origin;
run;


proc transpose data=test_dummy
			   out=test_transp
			   name=column_that_was_transposed 
  
;
by dummy;
var make;
id origin;
run;

Re: Duplicate errors with Proc Transpose

mreynaud — Wed, 02 Jun 2021 17:12:37 GMT

Yes, that is what I had in mind