not unique ID with proc transpose

[braam]

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_;
	run;

[PeterClemmensen]

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.

[braam]

Thanks for your willingness to help. I used sashelp.cars 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 sashelp.cars;
	keep make origin enginesize;
	if _n_ <= 40; 
	if enginesize= 3 then delete;
	run;
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;

[ballardw]

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?

[braam]

This is the last step to report my final data. What I expect from the code with sashelp.cars 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.

[ballardw]

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.

[ballardw]

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;
datalines;
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;
run;

[art297]

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

That said, the transpose macro (https://communities.sas.com/t5/SAS-Communities-Library/A-better-way-to-FLIP-i-e-transpose-make-wide-... ) 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; 
  set sashelp.cars;
  keep make origin enginesize type;
  if _n_ <= 40; 
  if enginesize= 3 then delete;
run;

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

Art, CEO, AnalystFinder.com