topic Re: Proc Transpose - two headers in SAS Programming

Proc Transpose - two headers

fai47 — Tue, 13 Jun 2017 14:58:48 GMT

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!

Re: Proc Transpose - two headers

Jagadishkatam — Tue, 13 Jun 2017 15:18:51 GMT

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;

Re: Proc Transpose - two headers

Reeza — Tue, 13 Jun 2017 15:27:47 GMT

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

Re: Proc Transpose - two headers

art297 — Tue, 13 Jun 2017 19:01:20 GMT

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

Re: Proc Transpose - two headers

fai47 — Wed, 21 Jun 2017 18:21:33 GMT

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!

Re: Proc Transpose - two headers

fai47 — Wed, 21 Jun 2017 20:05:16 GMT

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!

Re: Proc Transpose - two headers

art297 — Wed, 21 Jun 2017 20:13:00 GMT

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

Re: Proc Transpose - two headers

Tom — Wed, 21 Jun 2017 20:19:03 GMT

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.

Re: Proc Transpose - two headers

fai47 — Wed, 21 Jun 2017 20:20:50 GMT

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

Re: Proc Transpose - two headers

fai47 — Wed, 21 Jun 2017 20:23:33 GMT

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

Re: Proc Transpose - two headers

art297 — Wed, 21 Jun 2017 21:11:20 GMT

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