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.
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
Yes, that is what I had in mind
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.