topic Re: Can proc transpose do this in SAS Programming

Can proc transpose do this

zimcom — Thu, 26 Nov 2020 19:58:23 GMT

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!

Re: Can proc transpose do this

novinosrin — Thu, 26 Nov 2020 20:05:51 GMT

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;

Re: Can proc transpose do this

Kurt_Bremser — Thu, 26 Nov 2020 20:06:03 GMT

It can:

proc transpose
  data=have
  out=want (rename=(_name_=colname col1=value))
;
by subject;
var var:;
run;

Re: Can proc transpose do this

zimcom — Thu, 26 Nov 2020 20:09:22 GMT

@Kurt_Bremser @novinosrin

That was quick, thank you both!

Re: Can proc transpose do this

zimcom — Thu, 26 Nov 2020 20:14:04 GMT

what if the variables in daatset "have" are random variable name (not like Var1, Var2, Var3)?

Re: Can proc transpose do this

FreelanceReinh — Thu, 26 Nov 2020 22:55:46 GMT

@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.

Re: Can proc transpose do this

zimcom — Fri, 27 Nov 2020 13:25:36 GMT

Thank you! Really appreciated your help!

Re: Can proc transpose do this

Kurt_Bremser — Fri, 27 Nov 2020 13:28:32 GMT

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.