Proc Transpose - two headers

[fai47]

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!

[Jagadishkatam]

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;

[Reeza · Accepted solution]

@Jagadishkatam Too many output statements. 

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;

[fai47]

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!

[fai47]

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!

[art297]

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

[fai47]

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

[art297]

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

[Tom]

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.

Does that help you see the problem with your assignment statement?

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. 

[fai47]

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

[art297]

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