Tranpose Dataset Wide to Long with Multiple Headers

Reply
New Contributor
Posts: 2

Tranpose Dataset Wide to Long with Multiple Headers

[ Edited ]

Hello,

 

I am very stuck and could use some assiatnce. I am new to SAS and trying to transpose a number of datasets from wide to long. I have attached my input and desired output dataset (if possible, I would like to omit the Total column adn row, or dleete it altogether), which has the following variables/structure:

 

Row 1:  Product

Row 2: Product Type

column 1: Year

Cashflow

 

 

I would like to output to appear as:

Year     Product     ProductType     Amount

1          Product1    Producttype1     12

1          Produc1    Producttype2     14

1          Product2    Producttype1     16

2          Product1    Producttype1     13

 

 

I have been able to find code online, which works if we have only Product or ProductType. However, how could we tanspose both as it is the combination of Product and Product Type which is unique.

 

 

Super User
Posts: 10,460

Re: Tranpose Dataset Wide to Long with Multiple Headers

You didn't provide any variable names so this is more of a skeleton than a solution. This is onl one way. If you LOTS of columns that follow patterns it may be that we need more information to develop efficient code.

If your data is in a SAS dataset

 

data want;

   set have;

   length product $ 15  ProductType $ 25; /* These are NEW variables to have the information that currently have*/

   keep year product ProductType Value;

   Product="Product1"; ProductType="ProductType1";Value=Product1Producttype1value; OUTPUT;

   Product="Product1"; ProductType="ProductType2";Value=Product1Producttype2value; OUTPUT;

   Product="Product2"; ProductType="ProductType1";Value=Product2Producttype1value; OUTPUT;

run;

 

If you a talking about reading in a text file to create a dataset then there are other approaches but again need more details of the existing data.

 

New Contributor
Posts: 2

Re: Tranpose Dataset Wide to Long with Multiple Headers

Hello, sorry for not posting the dataset previously; I have updated the questiont o include both the dataset and variables. I hope this helps to clarify things.

Super User
Super User
Posts: 7,392

Re: Tranpose Dataset Wide to Long with Multiple Headers

Hi,

 

Its useful to put test data as a datastep so we don't have to type it in.  For your problem, just create a new variable and output it once for each data element:

data have;
year='Year 1'; product='Longevity Non-registered Annuity'; type='NonPar Non-adjustable'; cashflow=-336866.83791143; output;
year='Year 2'; product='Longevity Non-registered Annuity'; type='NonPar Non-adjustable'; cashflow=-326202.574166505; output;
year='Year 3'; product='Longevity Non-registered Annuity'; type='NonPar Non-adjustable'; cashflow=-314461.63098415; output;
year='Year 4'; product='Longevity Non-registered Annuity'; type='NonPar Non-adjustable'; cashflow=-303054.679895311; output;
year='Year 5'; product='Longevity Non-registered Annuity'; type='NonPar Non-adjustable'; cashflow=-292809.966960498; output;
year='Year 6'; product='Longevity Non-registered Annuity'; type='NonPar Non-adjustable'; cashflow=-281443.37734896; output;
year='Year 7'; product='Longevity Non-registered Annuity'; type='NonPar Non-adjustable'; cashflow=-270201.508816295; output;
year='Year 8'; product='Longevity Non-registered Annuity'; type='NonPar Non-adjustable'; cashflow=-260730.635466776; output;
year='Year 9'; product='Longevity Non-registered Annuity'; type='NonPar Non-adjustable'; cashflow=-249345.763336395; output;
year='Year 10'; product='Longevity Non-registered Annuity'; type='NonPar Non-adjustable'; cashflow=-241205.001898303; output;
year='Year 11'; product='Longevity Non-registered Annuity'; type='NonPar Non-adjustable'; cashflow=-229366.714741334; output;
year='Year 12'; product='Longevity Non-registered Annuity'; type='NonPar Non-adjustable'; cashflow=-219279.410773338; output;
year='Year 13'; product='Longevity Non-registered Annuity'; type='NonPar Non-adjustable'; cashflow=-211619.298715446; output;
run;

data want (keep=line);
  length line $200;
  set have;
  line=cats("Row 1: ",product); output;
  line=cats("Row 2: ",type); output;
  line=cats("Column 1: ",year); output;
  line=put(cashflow,best.); output;
run;
Ask a Question
Discussion stats
  • 3 replies
  • 521 views
  • 0 likes
  • 3 in conversation