Hi Community,
Kind of confused now, can proc transpose do this
have
Subject | Var1 | Var2 | Var3 | Var4 | Var5 | Var6 | Var7 | Var8 | Var9 | Var10 | Var11 | Var12 | Var13 | Var14 | Var15 | Var16 | Var17 | Var18 | Var19 | Var20 |
AB | 280 | 294 | 288 | 270 | 247 | 259 | 220 | 252 | 240 | 206 | 220 | 262 | 296 | 248 | 221 | 233 | 248 | 253 | 217 | 270 |
CD | 267 | 238 | 252 | 254 | 272 | 278 | 244 | 215 | 224 | 224 | 290 | 228 | 224 | 242 | 205 | 284 | 255 | 253 | 291 | 283 |
want
Subject | Colname | Value |
AB | Var1 | 280 |
AB | Var2 | 294 |
AB | Var3 | 288 |
AB | Var4 | 270 |
AB | Var5 | 247 |
AB | Var6 | 259 |
AB | Var7 | 220 |
AB | Var8 | 252 |
AB | Var9 | 240 |
AB | Var10 | 206 |
AB | Var11 | 220 |
AB | Var12 | 262 |
AB | Var13 | 296 |
AB | Var14 | 248 |
AB | Var15 | 221 |
AB | Var16 | 233 |
AB | Var17 | 248 |
AB | Var18 | 253 |
AB | Var19 | 217 |
AB | Var20 | 270 |
CD | Var1 | 267 |
CD | Var2 | 238 |
CD | Var3 | 252 |
CD | Var4 | 254 |
CD | Var5 | 272 |
CD | Var6 | 278 |
CD | Var7 | 244 |
CD | Var8 | 215 |
CD | Var9 | 224 |
CD | Var10 | 224 |
CD | Var11 | 290 |
CD | Var12 | 228 |
CD | Var13 | 224 |
CD | Var14 | 242 |
CD | Var15 | 205 |
CD | Var16 | 284 |
CD | Var17 | 255 |
CD | Var18 | 253 |
CD | Var19 | 291 |
CD | Var20 | 283 |
Thank you!
It can:
proc transpose
data=have
out=want (rename=(_name_=colname col1=value))
;
by subject;
var var:;
run;
data have;
input Subject $ Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8 Var9 Var10 Var11 Var12 Var13 Var14 Var15 Var16 Var17 Var18 Var19 Var20;
cards;
AB 280 294 288 270 247 259 220 252 240 206 220 262 296 248 221 233 248 253 217 270
CD 267 238 252 254 272 278 244 215 224 224 290 228 224 242 205 284 255 253 291 283
;
proc transpose data=have out=want(rename=(col1=value));
by subject;
var var1-var20;
run;
It can:
proc transpose
data=have
out=want (rename=(_name_=colname col1=value))
;
by subject;
var var:;
run;
That was quick, thank you both!
@zimcom wrote:
what if the variables in daatset "have" are random variable name (not like Var1, Var2, Var3)?
Hi @zimcom,
If all these variables are numeric (as they seem to be in your example), you can even omit the VAR statement, as PROC TRANSPOSE will transpose all numeric variables by default. Otherwise you can use a name range list of the form
var firstvar--lastvar;
(i.e., var1--var20 in your example), where the variable number (see column headed "#" in PROC CONTENTS output) defines the order of the list. The VAR statement also accepts combinations of several lists and variable names, not to mention macro variables containing tailored lists of variable names.
If there is any kind of pattern to the variable names, a query of DICTIONARY.COLUMNS in SQL can be used to store the variable names in a macro variable.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.