## Proc Transpose - two headers

Solved
Occasional Contributor
Posts: 5

# Proc Transpose - two headers

Hello,

I have data in excel that is horizontal that I need to transpose to be vertical.  In addition to the actual variable names in line 1, line 2 has dates and I would like to have date as a variable too.

Basically my data look like this:

 Name Price Price Price Quanity Quantity Quantity 1/1/2017 2/1/2017 3/1/2017 1/1/2017 2/1/2107 3/1/2017 A B C

But I want it to look like this:

 Name Date Price Quantity A 1/1/2017 B 2/1/2017 C 3/1/2017 A 1/1/2017 B 2/1/2017 C 3/1/2017 A 1/1/2017 B 2/1/2017 C 3/1/2017

Thank you!

Accepted Solutions
Solution
‎06-21-2017 02:21 PM
Super User
Posts: 23,296

## Re: Proc Transpose - two headers

``````Data want;
Set have;
Array _prices(*) price1-price10;
Array _quantity(*) quantity1-quantity10;

Do i=1 to dim(_prices);
Quantity = _quantity(i);
Prices = _prices(i);
Output;
End;

Run;
``````

All Replies
Posts: 1,147

## Re: Proc Transpose - two headers

``````data want;
set have;
array varp(*) price: ;
do i = 1 to dim(varp);
date=varp(i);
output;
end;
array varq(*) quantity: ;
do i = 1 to dim(varq);
date=varq(i);
output;
end;
run;``````
Thanks,
Jag
Solution
‎06-21-2017 02:21 PM
Super User
Posts: 23,296

## Re: Proc Transpose - two headers

``````Data want;
Set have;
Array _prices(*) price1-price10;
Array _quantity(*) quantity1-quantity10;

Do i=1 to dim(_prices);
Quantity = _quantity(i);
Prices = _prices(i);
Output;
End;

Run;
``````
Occasional Contributor
Posts: 5

## Re: Proc Transpose - two headers

This gets me pretty close - thank you!  I have a few extra rows at top and unneeded columns, but those are easy enough to delete.  I also don't end up with a date variable, but I can group them appropriately with the i variable and therefore process them correctly.  I can always go back and add in the date variables based on i.

Thank you all so much!

Occasional Contributor
Posts: 5

## Re: Proc Transpose - two headers

Maybe not as easy as I'd hoped.

So every record associated with i=1 is 1/1/2017, i=2 is 2/1/2017, etc.  So I was hoping just to add in a date variable as follows:

format date mmddyy10.;

if i=1 then date=01/01/2017;

else if i=2 then date = 02/01/2017;

else if i=3 then date=03/01/2017;

however, all my dates are showing as 01/01/1960.

I'm trying to merge join this file with another (left join using proc sql) using date as one of the keys, so somehow I need to get dates or some sort of translating language into the file I'm creating here.

If the date should be showing up as a variable in the initial file and I just need to tweak the code that is fine, or if there is a simpler way to hardcode this in that will suffice for now (I really only the need the actual date in there right now to compare files/testing, moving forward it would only be needed to troubleshoot which is why while not ideal, hardcoding will work for now).

Thanks!

PROC Star
Posts: 8,149

## Re: Proc Transpose - two headers

That's because SAS doesn't know how to read them correctly unless they're provide as sas dates (e.g., '1jan2017'd ).

However, an alternative that will require 11 fewer lines of code is:

`date=mdy(i,1,2017);`

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 5

## Re: Proc Transpose - two headers

Thank you Art - while your easy solution doesn't work necessarily with my data (with my example data, it's great), using the dates in the '1jan2017'd style works!

Now that I can join my datasets, I do see an issue with the earlier solution.  Those three 'extra' rows of data I was seeing (which just had dates, no names, prices or quantities) actually seemed to overwrite the first observation).

PROC Star
Posts: 8,149

## Re: Proc Transpose - two headers

Then, I suggest you try the solution I provided earlier in this thread. I think it's at the end of the current responses.

Art, CEO, AnalystFinder.com

Super User
Posts: 7,934

## Re: Proc Transpose - two headers

SAS dates are the number of days since '01JAN1960'd.  So it sounds like all of your date values are zero or between 0 and 1.

Here is another hint: what do you get when you divide 1 , 2 or 3 by 2,017 ?

One more hint: how do you enter a date literal in SAS code?

``````if i=1 then date='01JAN2017'd;
else if i=2 then date = '01FEB2017'd;
else if i=3 then date= '01MAR2017'd;``````

I wouldn't recommend attaching either MMDDYY or DDMMYY format to dates because you will just end up confusing half of the world.  Use either DATE9. or YYMMDD10.

Occasional Contributor
Posts: 5

## Re: Proc Transpose - two headers

Thank you Tom!  I'll definitely keep that advice in mind going forward!

PROC Star
Posts: 8,149

## Re: Proc Transpose - two headers

If I correctly understand what you are trying to do, the following will accomplish the task:

```proc import datafile='/folders/myfolders/book1.xlsx' out=have
dbms=xlsx replace;
sheet='Sheet1';
run;

data null;
set have (obs=1);
array _price(*) \$ price:;
array _quantity(*) \$ quan:;
length forexec \$200;
length forxprep1 forxprep2 \$80;
call execute('data want (keep=Name date price quantity);');
call execute('set have (firstobs=2 rename=(');
do i=1 to dim(_price);
forxprep1=catt(vname(_price(i)),'=_p',i);
forxprep2=catt(vname(_quantity(i)),'=_q',i);
forexec=catx(' ',forexec,forxprep1,forxprep2);
end;
call execute(forexec);
call execute(')); array _price(*) \$ _p:; array _quantity(*) \$ _q:;');
call execute('format date date9.;');
do i=1 to dim(_price);
forexec=catt('date=',input(_price(i),mmddyy10.),';');
call execute(forexec);
forexec=catt('Quantity = input(_quantity(',i,'),8.);');
call execute(forexec);
forexec=catt('Price = input(_price(',i,'),8.); output;');
call execute(forexec);
end;
call execute('run;');
run;
```

Of course some of the input statements might have to be modified dependent upon how your Workbook actually imports.

Art, CEO, AnalystFinder.com

☑ This topic is solved.