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;
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;
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?
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.....
The first row contains variable names while the second and third rows are the Origin and Enginesize columns of the original dataset.
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;
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
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.
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.