Transposing with long variable names

Reply
Occasional Contributor
Posts: 6

Transposing with long variable names

HI all

I am trying to transpose a dataset with two variables, with the first column taken as the variable names in the transposed dataset. I've done this before, should be straightfoward, but this time is different because the first column I am trying to transpose is quite long. The identifiers at the end get cut off (I think?) and there is a duplicate ID value error. How can I set the length of the variable names in the transposed dataset?

My code is as follows, with a sample of the dataset underneath.

proc transpose
data=work.tempBound3
out=work.tempBound4
;
ID _Ratio_temp;
var Bound;

run;


OperatingCashflow_FinanceCosts_LB-2
OperatingIncome_TotalRevenue_LB-0.5
ShortTermDebt_LongTermDebt_LB.
TotalDebt_EBITDA_LB-1
TotalDebt_NetIncome_LB-2
TotalDebt_OperatingCashflow_LB-1
TotalDebt_OperatingProfit_LB-3
TotalDebt_ShareholdersEquity_LB-0.5
TotalDebt_TotalEquity_LB-1
TotalDebt_TotalTangibleAssets_LB.
TotalLiabilities_TangibleAssets_LB.
TotalLiabilities_TotalAssets_LB.
TotalLiabilities_TotalEquity_LB-1
OperatingCashflow_FinanceCosts_UB100
OperatingIncome_TotalRevenue_UB2
ShortTermDebt_LongTermDebt_UB.
TotalDebt_EBITDA_UB15
TotalDebt_NetIncome_UB35
TotalDebt_OperatingCashflow_UB15
TotalDebt_OperatingProfit_UB50
TotalDebt_ShareholdersEquity_UB3
TotalDebt_TotalEquity_UB8
TotalDebt_TotalTangibleAssets_UB.
TotalLiabilities_TangibleAssets_UB.
TotalLiabilities_TotalAssets_UB.
TotalLiabilities_TotalEquity_UB30



Super User
Posts: 5,260

Re: Transposing with long variable names

I'm always confused when people wish to transpose rows to columns, generally there is no need. So what do you wish to do with the transposed data?

A dull answer is to fix the id columns so it has max 32 char. If your values a repeated, it could be done quite easy using a format.

Data never sleeps
Super User
Super User
Posts: 6,502

Re: Transposing with long variable names

Why not just use your long strings as labels instead of variable names?

data have ;

  length _ratio_temp $50 bound 8;

  infile cards dsd dlm='|';

  input _ratio_temp bound;

  row+1;

cards;

OperatingCashflow_FinanceCosts_LB|-2

OperatingIncome_TotalRevenue_LB|-0.5

ShortTermDebt_LongTermDebt_LB|.

TotalDebt_EBITDA_LB|-1

TotalDebt_NetIncome_LB|-2

TotalDebt_OperatingCashflow_LB|-1

TotalDebt_OperatingProfit_LB|-3

TotalDebt_ShareholdersEquity_LB|-0.5

TotalDebt_TotalEquity_LB|-1

TotalDebt_TotalTangibleAssets_LB|.

TotalLiabilities_TangibleAssets_LB|.

TotalLiabilities_TotalAssets_LB|.

TotalLiabilities_TotalEquity_LB|-1

OperatingCashflow_FinanceCosts_UB|100

OperatingIncome_TotalRevenue_UB|2

ShortTermDebt_LongTermDebt_UB|.

TotalDebt_EBITDA_UB|15

TotalDebt_NetIncome_UB|35

TotalDebt_OperatingCashflow_UB|15

TotalDebt_OperatingProfit_UB|50

TotalDebt_ShareholdersEquity_UB|3

TotalDebt_TotalEquity_UB|8

TotalDebt_TotalTangibleAssets_UB|.

TotalLiabilities_TangibleAssets_UB|.

TotalLiabilities_TotalAssets_UB|.

TotalLiabilities_TotalEquity_UB|30

;;;;

proc transpose data=have out=want prefix=ratio;

  id row;

  idlabel _ratio_temp;

  var bound;

run;

proc print; run;

proc print label; run;

Ask a Question
Discussion stats
  • 2 replies
  • 202 views
  • 0 likes
  • 3 in conversation