BookmarkSubscribeRSS Feed
Quartz | Level 8

I would like to run the following code, but the ID variable is not unique. What I would like to achieve is that SAS automatically assigns a number from 1 to N to new variables in the transposed data.


Let's say, my ID includes CAR three times. Then I would like to have CAR1, CAR2, CAR3 as variables in the transposed data. Can it be done by SAS automatically? Many thanks.



proc transpose data= w_ name= model  out= w_2 (drop= _label_);  
	id RowName model;
	var _all_;


Super User

Can you provide an example of what your data looks like? And what your desired result looks like from that data? Makes it much easier to provide a usable code answer.

Quartz | Level 8

Thanks for your willingness to help. I used to describe what you would like to have. The below code doesn't work because MAKE isn't unique. As provided, Acura appears seven times. When using proc transpose, I would like to generate Acura1-Acura7 variables automatically in the transposed set. Similarly, I'd like to make several Audi variables and BMW ones.


option notes;
data temp; set;
	keep make origin enginesize;
	if _n_ <= 40; 
	if enginesize= 3 then delete;
proc print data= temp; run;

Obs Make Origin EngineSize 
1 Acura Asia 3.5 
2 Acura Asia 2.0 
3 Acura Asia 2.4 
4 Acura Asia 3.2 
5 Acura Asia 3.5 
6 Acura Asia 3.5 
7 Acura Asia 3.2 
8 Audi Europe 1.8 
9 Audi Europe 1.8 
10 Audi Europe 2.7 
11 Audi Europe 4.2 
12 Audi Europe 4.2 
13 Audi Europe 4.2 
14 Audi Europe 4.2 
15 Audi Europe 1.8 
16 Audi Europe 1.8 
17 Audi Europe 3.2 
18 Audi Europe 4.2 
19 BMW Europe 4.4 
20 BMW Europe 2.5 
21 BMW Europe 2.5 
22 BMW Europe 2.5 
23 BMW Europe 2.5 
24 BMW Europe 2.5 
25 BMW Europe 4.4 
26 BMW Europe 4.4 

proc transpose data= temp out= temp_tr;
	id make; 
	var _all_; run;

Super User

And exactly what do you expect the data to look like on output?

With a character variable Origin and a numeric variable Enginesize it sort of doesn't make a lot of sense to have a variable "Acura1" with values of "Asia" and "3.5", and with Proc transpose the engine size will become character.


And _all_ on the VAR statement will also try to include the ID and/or BY variables in the transposition. So we really need to know what you are expecting the output to look like.


And are you trying to make a data set for further manipulation or a report that people read?

Quartz | Level 8

This is the last step to report my final data. What I expect from the code with is:


Acura1 Acura2 Acura3.... Acura7 Audi1 .... Audi11.....
Asia Asia
3.5 2.0

 The first row contains variable names while the second and third rows are the Origin and Enginesize columns of the original dataset.

Super User

I think that you need to be a bit more explicit about showing what you want. Maybe pick on no more than 3 levels of 2 makes so it can be done by hand. And show the entire example.




Super User

The option LET on the Proc Transpose statement will allow multiple values of id variables to get processed. BUT may not result in what you want.


Example data of before and after are almost a requirement to provide code to reshape data.

To get variables like CAR1 CAR2 with

id RowName model;

your variable Rowname would have to be CAR and model would have values 1 2 etc.

BUT you will only get one output value per actual ide value. Example:

data work.have;
  input rowname $ model $ value;
car 1 123
car 1 456
car 2 1.34
car 2 6.9
truck 1 456

proc transpose data=work.have out=work.trans let;
  id rowname model;
  var value;
Opal | Level 21

Impossible to know what you want without seeing the result you want from the data you provided.


That said, the transpose macro ( ) might provide the solution that you need. It gives you such capabilities as sorting the data, keeping character and numeric variables in the original formats, and assigning unique id numbers.


For example, something like the following might be all that you need:

data temp; 
  keep make origin enginesize type;
  if _n_ <= 40; 
  if enginesize= 3 then delete;

%transpose(data=temp, by=origin make, out=temp_tr, 
    newid=idnum,autovars=all, sort=yes)

Art, CEO,




Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 4 in conversation